YTD For Two Dates

[Anonymous]

Hello,

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:

1) MTD

Customer	Order Intake	Revenue
Homer Simpson	111,037	19,393

2) QTD

Customer	Order Intake	Revenue
Homer Simpson	129,701	19,393

3) YTD

Customer	Order Intake	Revenue
Homer Simpson	334,974	179,933

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].

Problem Statement

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.

[v-jayw-msft]

Hi @Anonymous ,

If the problem was solved, please mark this post as "Solved"

Best Regards,

Jay

[Anonymous]

to which post? the solution from @amitchandak was not the one I need, because I figured out this earlier (my measure was referring to the wrong table). 

[amitchandak]

@Anonymous , your own reply telling you were using wrong date.  I marked it. 

[amitchandak]

@Anonymous , 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

[Anonymous · Accepted solution]

this is my bad, I was referring to the wrong table, apologies! the issue has been solved.