Forum Discussion
Helper IApplying Year filter to Running Total
I'm struggling to get my running total to only include data from this fiscal year. I have data from previous years that I don't want to include in this running total but the following DAX only filters for the calendar year and not starting from April.
How do I force it to only use 2020-2021 data AND start in April? Thanks!
5 Replies
- amitchandak
Super User
khaycock , 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-e3174b39f38aTo 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
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?
