Forum Discussion
Helper IApplying Year filter to Running Total
Super Userkhaycock , Try datesytd with date calendar
Create
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"3/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
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/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
khaycockThis works perfectly thank you! Is there anyway to keep the months in the future blank for CYTD rather than show the same figure?
jthomsonSolution Sage
You could try taking the formula you have now and use conditional logic to compare the date in your date table to today, something like:
NewMeasure = if('Date'[Date] > today(),blank(),[YTD Sales])
- khaycock
The IF statement on this measure doesn't let me use the Calendar Date field, it only lets me pick an existing Measure..
- khaycock
Is there a way of using this method with a rolling target figure instead?
I have a table called Targets which holds Date (1st of each month), Region and Amount. The amount is the same for every month but the user wants the Target line to be a rolling total each month, so each month the amount increases with April's figure.
Example 1 is what I have the data stored like
Example 2 is what I want the figures to look like in the dashboard:
When I try and amend the YTD figure you gave me, I get a total of all the months together rather than a rolling addition of each month. I know this is because the measure it summing the Target Amount, but I don't know how I can do the Measure without summing?