Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate YTD based on Month slicer

Hi,

 

I want to calculate YTD based on month slicer. 

1.PNG

In the image, YTD for customer is 6 for the month 1 which is correct. But when I select the month 2 in the below image, the YTD is not getting added with the previous month. Instead it is taking the selected month value. 

2.PNG

 

How do I modify the measure to fix this?

 

Inspections YTD = TOTALYTD([Inspections/Month],('SMS Inspection'[date]))
 
Monthly slicer table is Ref Calender.
 
Thank you. 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this measure

=CALCULATE([Inspections/Month],DATESYTD(Calendar[Date]))

There should be a relationship from the Date column of the Inspection Date table to the Date column of the Ref Calendar Table.

If this does not help, then share the link from where i can download the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
William_wei
Regular Visitor

Hi, 

 

 

I am working on the TOTALYTD function as well. I have exactly the same problem as yours. Only data for that selected month is displayed without adding the data of previous months together. I have managed to solve it by replacing the "timetable.date"  in TOTALYTD formula with "original table.date". (I assumed that you created a timetable and you used the "data" from the timetable generated by formula)

 

What I did is to make sure that the date in totalytd() is actually in the same table where the "month" column is used for the month slicer. Hope this can help you! Thx!

amitchandak
Super User
Super User

@Anonymous , Please use a date calendar and join your date with that. and try like

Inspections YTD = TOTALYTD([Inspections/Month],('Date'[date]))

You can also try datestyd

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

It is also explained on my blog :https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885

 

Appreciate your Kudos.

Ashish_Mathur
Super User
Super User

Hi,

Try this measure

=CALCULATE([Inspections/Month],DATESYTD(Calendar[Date]))

There should be a relationship from the Date column of the Inspection Date table to the Date column of the Ref Calendar Table.

If this does not help, then share the link from where i can download the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

HI @ashish Mathur,

 

This is a nice clean formula, thank you. 

 

I can't figure out hos to do this for Prior Year to Date. The report I have has a month filter and I need formulas for prior year that will ignore the month filter. I tried PREVIOUSYEAR() but that's total prior year.

Hi,

You may try SAMEPERIODLASTYEAR().


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
kentyler
Solution Sage
Solution Sage

When this measure is executed for each row

Inspections YTD = TOTALYTD([Inspections/Month],('SMS Inspection'[date]))

then the only month that is available in the filter context is the month of that row

to give it access to all the months in the year you will have to tell it to consider ALL()

Inspections YTD = TOTALYTD([Inspections/Month],('SMS Inspection'[date]),ALL('SMS Inspection'[date]))

I haven't actually tried that with your data. But I think something along those lines is what you will need to do.

 

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

@kentyler I have tried what you suggested. But no luck. It is still displaying the value for the month. 

 

3.PNG

maybe it is Inspections/Month that you have to do ALL() on





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.