Forum Discussion
Running Total with three years
Hi Anonymous,
Apologize for the delayed response. After thoroughly reviewing the details you provided, I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
Hi Anonymous - can you change the logic for previous year as below:
PreviousYearQuarter =
VAR SelectedQuarter = SELECTEDVALUE(DNM_DRILL_THROUGH[FISCAL_QTR]) -- Selected quarter from slicer
VAR CurrentYear = INT(LEFT(SelectedQuarter,4)) -- Extract year
VAR CurrentQtr = RIGHT(SelectedQuarter,2) -- Extract Q1, Q2, etc.
VAR PrevYearQtr = FORMAT(CurrentYear - 1, "0000") & CurrentQtr -- Get previous year same quarter
-- Find max week number for selected quarter
VAR MaxWeekSelectedQtr =
MAXX(
FILTER(DNM_DRILL_THROUGH, DNM_DRILL_THROUGH[FISCAL_QTR] = SelectedQuarter),
INT(RIGHT(DNM_DRILL_THROUGH[FISCAL_WEEK_NUMBER_D], 2))
)
RETURN
CALCULATE(
[FilterBookingAmount], -- Your measure for Booking Amount
ALLSELECTED(DNM_DRILL_THROUGH[FISCAL_QTR]),
DNM_DRILL_THROUGH[FISCAL_QTR] = PrevYearQtr,
-- Ensure previous year always shows full 13 weeks, while selected quarter is restricted dynamically
IF(
SelectedQuarter = PrevYearQtr,
INT(RIGHT(DNM_DRILL_THROUGH[FISCAL_WEEK_NUMBER_D],2)) <= 13, -- Show full 13 weeks for previous year
INT(RIGHT(DNM_DRILL_THROUGH[FISCAL_WEEK_NUMBER_D],2)) <= MaxWeekSelectedQtr -- Restrict only selected quarter
)
)
Hope this helps. . please check