cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
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

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!