Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to make a year to date for the same period last year calculation. I want to present this year and previous year next to each other in a bar chart. The periods are business defined. I cannot use standard time-intelligence functions. I have created the next test measure because my original measure in the bar chart returns the same result for each period in the previous year.
YTD Last Date Last Year =
VAR RemoveFilter =
FILTER (
ALL ( Date);
Date[Year] <= YEAR ( VALUES ( Last Refresh'[Last Refresh] ) ) -1
&& Date[Date] <= MAX ( Date[Date] )
)
VAR Last Date =
CALCULATE ( LASTDATE ( Date[Date] ); RemoveFilter )
RETURN
Last Date Last Year
My intention is that the test measure returns the last date of each period for the previous year. It didn't work that way though. It returns for each period the last day of the year (screenshot below). I've also made a measure in which I removed the '-1' in the formula. This measure does return the last date of the period for this year. How can I create the measure in such a way that it returns the last day of the period in the previous year?
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
I tried several different calculations, but nothing works. I 've given it a lot of time but i do not have the feeling that I really understand how the syntax works. I've applied the syntax in Time Intelligence "The Hard Way" in the next measure and tried to compute a first date, last date and row count on the date table in order to check if the calculation is correct.
VAR MAX_Year=
The rowcount suggests that not all rows are counted in the datetable that are before the last date of each period in this or previous year (not really a ytd, but a count of all mutations over all years). What I want to achieve is to:
Perhaps I need to find another approach, or is this possible? Because periods can vary in start- and enddate I cannot use standard time based calculations I guess.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |