Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I want to calculate YTD based on month slicer.
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.
How do I modify the measure to fix this?
Solved! Go to Solution.
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.
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!
@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.
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.
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().
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.
Help when you know. Ask when you don't!
@kentyler I have tried what you suggested. But no luck. It is still displaying the value for the month.
maybe it is Inspections/Month that you have to do ALL() on
Help when you know. Ask when you don't!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |