I hope there is someone who could help with this. I have two fact tables.
Table 1 contains Work Order (Integer), WO Created (date) and Order Intake (decimal), Customer (text):
|Work Order||WO Created||Customer||Order Intake|
|75884424||27/01/2020 12:50||Homer Simpson||680.76|
|75885363||30/01/2020 19:11||Homer Simpson||6,803.09|
|75885550||31/01/2020 13:39||Homer Simpson||1,275.00|
|75885553||31/01/2020 13:50||Homer Simpson||1,170.30|
|75885560||31/01/2020 13:57||Homer Simpson||1,278.76|
|75887779||04/02/2020 12:45||Homer Simpson||3,967.12|
|75887784||04/02/2020 12:53||Homer Simpson||1,024.80|
|75889767||14/02/2020 16:44||Homer Simpson||175.40|
|75891947||27/02/2020 18:51||Homer Simpson||905.74|
|75891979||28/02/2020 09:44||Homer Simpson||2,211.51|
|75895536||09/03/2020 10:37||Homer Simpson||1,722.70|
|75895564||09/03/2020 11:12||Homer Simpson||5,165.60|
|75897793||24/03/2020 12:06||Homer Simpson||4,425.22|
|75898001||25/03/2020 14:43||Homer Simpson||8,079.57|
|75898078||26/03/2020 11:59||Homer Simpson||42,224.30|
|75898088||26/03/2020 12:27||Homer Simpson||30,903.40|
|75898096||26/03/2020 12:58||Homer Simpson||87,411.96|
|75903943||20/04/2020 09:32||Homer Simpson||3,088.80|
|75904136||21/04/2020 09:27||Homer Simpson||750.00|
|75904835||24/04/2020 13:30||Homer Simpson||13,551.94|
|75905019||27/04/2020 11:23||Homer Simpson||93,646.50|
|75905988||04/05/2020 15:49||Homer Simpson||252.50|
|75910123||27/05/2020 10:42||Homer Simpson||4,205.62|
|75910145||27/05/2020 11:37||Homer Simpson||452.41|
|75910147||27/05/2020 11:43||Homer Simpson||521.44|
|75910151||27/05/2020 12:15||Homer Simpson||13,231.98|
|75914744||18/06/2020 11:56||Homer Simpson||5,847.36|
Table 2 contains Work Order (Integer), Period (date) and Revenue (decimal).
Table 3 is the dim_Calendar, calendarauto() table.
What I want to have is the table visual that has the following fields: Customer, Order Intake, Revenue:
1) if MonthToDate,
2) if QuarterToDate,
3) if YearToDate.
Therefore, the desired output for April would be:
I have the following measures:
Revenue-Sum = sum(fact_Revenue[Revenue]) Revenue = switch(SELECTEDVALUE(dim_Scenario[Scenario]), "MTD",TOTALMTD([Revenue-Sum],'dim_Calendar'[Date]), "QTD",TOTALQTD([Revenue-Sum],'dim_Calendar'[Date]), "YTD",TOTALYTD([Revenue-Sum],'dim_Calendar'[Date])) Order-Intake-Sum = sum(fact_Install[Order Intake]) Order-Intake-CY = switch(SELECTEDVALUE(dim_Scenario[Scenario]), "MTD",TOTALMTD([Order-Intake-Sum],'dim_Calendar'[Date]), "QTD",TOTALQTD([Order-Intake-Sum],'dim_Calendar'[Date]), "YTD",TOTALYTD([Order-Intake-Sum],'dim_Calendar'[Date]))
There is also a many to one relationship between dim_Calendar[Date] and fact_Revenue[Period] and likewise between dim_Calendar[Date] and fact_Install[WO Created].
Order intake measure works correctly, it is showing as I want MTD/QTD/YTD. The problem is with the Revenue measure.
For example, when I select April MTD, it is showing Order Intake 111,037, which is correct. But, the revenue is showing 16,641 which is wrong.
Solved! Go to Solution.
@marekmarek , looking at data , WO Created has a timestamp. And was expecting that to be wrong unless you create a date column and joined with that.
Check if the period (date in Work Order ) has timestamp .
if so create a date and then join with date of date table and try
period date = [period].Date
Hi @marekmarek ,
If the problem was solved, please mark this post as "Solved"
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.