Overall YTD - Revision history

Hendry.mwamburi: /* Calculations */

2025-11-25T09:56:06Z

Calculations

← Older revision		Revision as of 11:56, 25 November 2025
Line 238:		Line 238:
	) </pre>\|\| The formula calculates the total margin in euros where either the booked timestamp or the confirmed timestamp is not null.		) </pre>\|\| The formula calculates the total margin in euros where either the booked timestamp or the confirmed timestamp is not null.
	\|-		\|-
	\| ~~Total Cost in euros \|~~\|<pre>~~Total Cost (EUR booked or confirmed) =~~		\| Origin destination
	~~SUMX(~~		pair
	~~FILTER(~~		\|<pre> each [origin] & " - " & [destination]</pre>\|\| The formula concatenate origin column and destination column
	~~'Dimreporting_db quotation_totals',~~
	~~'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)~~
	),
	~~SWITCH(~~
	~~TRUE(),~~
	~~NOT ISBLANK('Dimreporting_db quotation_totals'~~[~~booked_at~~]),
	~~'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total]~~
	~~/ 'Dimreporting_db quotation_totals'[booked_customer_invoice_currency_to_euro_exchange_rate],~~
	~~ISBLANK('Dimreporting_db quotation_totals'[booked_at])~~
	&& ~~NOT ISBLANK('Dimreporting_db quotation_totals'~~[~~confirmed_at~~]),
	~~'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total]~~
	~~/ 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate],~~
	0
	)
	)</pre>\|\| The formula ~~calculates the total cost in euros where either the booked timestamp or the confirmed timestamp is not null.~~

	\|}		\|}

Hendry.mwamburi: /* Calculations */

2025-11-25T09:40:22Z

Calculations

← Older revision		Revision as of 11:40, 25 November 2025
Line 256:		Line 256:
	)		)
	)</pre>\|\| The formula calculates the total cost in euros where either the booked timestamp or the confirmed timestamp is not null.		)</pre>\|\| The formula calculates the total cost in euros where either the booked timestamp or the confirmed timestamp is not null.
	\|-
	~~\| Total Margin in euros % \|\|<pre>Margin (%) =~~
	~~DIVIDE(~~
	~~[Total Revenue (EUR Booked or Confirmed)] - [Total Cost (EUR booked or confirmed)],~~
	~~[Total Revenue (EUR Booked or Confirmed)],~~
	0
	~~) </pre>\|\| The formula calculates the total margin in euros where either the booked timestamp or the confirmed timestamp is not null.~~
	\|}		\|}

Hendry.mwamburi: /* Calculations */

2025-11-25T09:39:34Z

Calculations

@@ Line 211: / Line 211: @@
      ),
 )</pre>|| The formula calculates the gross profit in euros where either the booked timestamp or the confirmed timestamp is not null.
 |-
 | Total Cost in euros ||<pre>Total Cost (EUR booked or confirmed) =

Hendry.mwamburi: /* Calculations */

2025-11-25T09:34:52Z

Calculations

← Older revision		Revision as of 11:34, 25 November 2025
Line 231:		Line 231:
	)</pre>\|\| The formula calculates the total cost in euros where either the booked timestamp or the confirmed timestamp is not null.		)</pre>\|\| The formula calculates the total cost in euros where either the booked timestamp or the confirmed timestamp is not null.
	\|-		\|-
	\| ~~Gross Profit~~ in euros ~~booked or confirmed~~ \|\|<pre>~~Gross Profit~~ (~~EUR booked or Confirmed~~)2 =		\| Total Margin in euros % \|\|<pre>Margin (%) =
	~~COALESCE~~(		DIVIDE(
	~~SUMX(~~		[Total Revenue (EUR Booked or Confirmed)] - [Total Cost (EUR booked or confirmed)],
	~~FILTER(~~		[Total Revenue (EUR Booked or Confirmed)],
	~~'Dimreporting_db quotation_totals',~~		0
	~~'Dimreporting_db quotation_totals'~~[~~created_at] >= DATE~~(~~2024, 1, 1)~~		) </pre>\|\| The formula calculates the total margin in euros where either the booked timestamp or the confirmed timestamp is not null.
	),
	~~SWITCH(~~
	~~TRUE(),~~
	~~NOT ISBLANK('Dimreporting_db quotation_totals'[booked_at]~~),
	~~('Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total~~]
	- ~~'Dimreporting_db quotation_totals'~~[~~customer_invoice_currency_cost_total]~~)
	~~/ 'Dimreporting_db quotation_totals'[booked_customer_invoice_currency_to_euro_exchange_rate~~],
	~~ISBLANK('Dimreporting_db quotation_totals'~~[~~booked_at])~~
	~~&& NOT ISBLANK~~(~~'Dimreporting_db quotation_totals'[confirmed_at]),~~
	~~('Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]~~
	~~- 'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total]~~)
	~~/ 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate~~],
	0
	)
	),
	0)</pre>\|\| The formula calculates the ~~gross profit~~ in euros where either the booked timestamp or the confirmed timestamp is not null.
	\|}		\|}

Hendry.mwamburi: /* Calculations */

2025-11-25T09:31:16Z

Calculations

@@ Line 212: / Line 212: @@
 )</pre>|| The formula calculates the gross profit in euros where either the booked timestamp or the confirmed timestamp is not null.
 |-
-| Gross Profit in euros booked or confirmed ||<pre>Gross Profit (EUR booked or Confirmed)2 =
+| Total Cost in euros ||<pre>Total Cost (EUR booked or confirmed) =
-COALESCE(
+SUMX(
-     SUMX(
+     FILTER(
-         FILTER(
+         'Dimreporting_db quotation_totals',
-            'Dimreporting_db quotation_totals',
+        'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
      ),
-)</pre>|| The formula calculates the gross profit in euros where either the booked timestamp or the confirmed timestamp is not null.
+    SWITCH(
 |-
 | Gross Profit in euros booked or confirmed ||<pre>Gross Profit (EUR booked or Confirmed)2 =

Hendry.mwamburi: /* Calculations */

2025-11-25T09:29:41Z

Calculations

@@ Line 188: / Line 188: @@
 ),
 )</pre>|| The formula calculates the total revenue in euros where either the booked timestamp or the confirmed timestamp is not null.
 |-
 | Gross Profit in euros booked or confirmed ||<pre>Gross Profit (EUR booked or Confirmed)2 =

Hendry.mwamburi: /* Data Transformation Applied */

2025-11-25T09:26:42Z

Data Transformation Applied

← Older revision		Revision as of 11:26, 25 November 2025
Line 116:		Line 116:
	\|This query retrieves columns from the quotation table and converts the chargeable weight from pounds to kilograms. It filters records where the creation date is on or after 2024 and the type equals 8, excluding lost and deleted entries.		\|This query retrieves columns from the quotation table and converts the chargeable weight from pounds to kilograms. It filters records where the creation date is on or after 2024 and the type equals 8, excluding lost and deleted entries.
	\|-		\|-
	\| ~~Sites~~\|\| SELECT ~~id, name~~ FROM ~~sites~~		\| reporting db quotation totals\|\| SELECT * FROM reporting_db_quotation_totals
	\|This selects ~~site id and name~~ from ~~the site table.~~		\|This selects data from reporting_db_quotation_totals
	\|-		\|-
	\| Sites\|\| SELECT id, name FROM sites		\| Sites\|\| SELECT id, name FROM sites

Hendry.mwamburi: /* Data Transformation Applied */

2025-11-25T09:03:25Z

Data Transformation Applied

← Older revision		Revision as of 11:03, 25 November 2025
Line 115:		Line 115:

	\|This query retrieves columns from the quotation table and converts the chargeable weight from pounds to kilograms. It filters records where the creation date is on or after 2024 and the type equals 8, excluding lost and deleted entries.		\|This query retrieves columns from the quotation table and converts the chargeable weight from pounds to kilograms. It filters records where the creation date is on or after 2024 and the type equals 8, excluding lost and deleted entries.
			\|-
			\| Sites\|\| SELECT id, name FROM sites
			\|This selects site id and name from the site table.
	\|-		\|-
	\| Sites\|\| SELECT id, name FROM sites		\| Sites\|\| SELECT id, name FROM sites

Hendry.mwamburi: /* Calculations */

2025-11-25T09:01:29Z

Calculations

@@ Line 186: / Line 186: @@
 )</pre>|| The formula calculates the total revenue in euros where either the booked timestamp or the confirmed timestamp is not null.
 |-
-| Total Revenue in euros booked or confirmed ||<pre>Total Revenue (EUR Booked or Confirmed) =
+| Gross Profit in euros booked or confirmed ||<pre>Gross Profit (EUR booked or Confirmed)2 =
 COALESCE(
-SUMX(
+    SUMX(
-    FILTER(
+        FILTER(
-        'Dimreporting_db quotation_totals',
+            'Dimreporting_db quotation_totals',
-        'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
+            'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
      ),
-    SWITCH(
+)</pre>|| The formula calculates the gross profit in euros where either the booked timestamp or the confirmed timestamp is not null.
 |}

Hendry.mwamburi: /* Calculations */

2025-11-25T08:57:54Z

Calculations

@@ Line 164: / Line 164: @@
      NOT ISBLANK(Quotation[quoted_at]),
      Quotation[type]=8),0)</pre>|| The formula calculates the total chargeable weight in kilograms
 |-
 | Total Revenue in euros booked or confirmed ||<pre>Total Revenue (EUR Booked or Confirmed) =