cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

YTD For Two Dates

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 OrderWO CreatedCustomerOrder Intake
7588442427/01/2020 12:50Homer Simpson680.76
7588536330/01/2020 19:11Homer Simpson6,803.09
7588555031/01/2020 13:39Homer Simpson1,275.00
7588555331/01/2020 13:50Homer Simpson1,170.30
7588556031/01/2020 13:57Homer Simpson1,278.76
7588777904/02/2020 12:45Homer Simpson3,967.12
7588778404/02/2020 12:53Homer Simpson1,024.80
7588976714/02/2020 16:44Homer Simpson175.40
7589194727/02/2020 18:51Homer Simpson905.74
7589197928/02/2020 09:44Homer Simpson2,211.51
7589553609/03/2020 10:37Homer Simpson1,722.70
7589556409/03/2020 11:12Homer Simpson5,165.60
7589779324/03/2020 12:06Homer Simpson4,425.22
7589800125/03/2020 14:43Homer Simpson8,079.57
7589807826/03/2020 11:59Homer Simpson42,224.30
7589808826/03/2020 12:27Homer Simpson30,903.40
7589809626/03/2020 12:58Homer Simpson87,411.96
7590394320/04/2020 09:32Homer Simpson3,088.80
7590413621/04/2020 09:27Homer Simpson750.00
7590483524/04/2020 13:30Homer Simpson13,551.94
7590501927/04/2020 11:23Homer Simpson93,646.50
7590598804/05/2020 15:49Homer Simpson252.50
7591012327/05/2020 10:42Homer Simpson4,205.62
7591014527/05/2020 11:37Homer Simpson452.41
7591014727/05/2020 11:43Homer Simpson521.44
7591015127/05/2020 12:15Homer Simpson13,231.98
7591474418/06/2020 11:56Homer Simpson5,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

CustomerOrder IntakeRevenue
Homer Simpson                     111,037                       19,393

 

2) QTD

 

CustomerOrder IntakeRevenue
Homer Simpson                     129,701                       19,393

 

3) YTD

 

CustomerOrder IntakeRevenue
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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper IV
Helper IV

Re: YTD For Two Dates

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

View solution in original post

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: YTD For Two Dates

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper IV
Helper IV

Re: YTD For Two Dates

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

View solution in original post

Highlighted
Community Support
Community Support

Re: YTD For Two Dates

Hi @marekmarek ,

 

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

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Helper IV
Helper IV

Re: YTD For Two Dates

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

Highlighted
Super User IX
Super User IX

Re: YTD For Two Dates

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors