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
Please use this DAX to get the correct answer
Cumulative Booking Amount =
VAR selected_quarter = SELECTEDVALUE(DNM_DRILL_THROUGH[FISCAL_QTR])
VAR current_year = INT(LEFT(selected_quarter, 4)) -- Extracts the year
VAR current_qtr = RIGHT(selected_quarter, 2) -- Extracts the quarter
-- Get previous and previous-to-previous year quarters
VAR pqtr = FORMAT(current_year - 1, "0000") & current_qtr
VAR ppqtr = FORMAT(current_year - 2, "0000") & current_qtr
-- Get max week available for the selected quarter
VAR max_week_selected =
MAXX(
FILTER(DNM_DRILL_THROUGH, DNM_DRILL_THROUGH[FISCAL_QTR] = selected_quarter),
INT(RIGHT(DNM_DRILL_THROUGH[FISCAL_WEEK_NUMBER_D], 2))
)
-- Compute Cumulative Booking Amount
RETURN
CALCULATE(
[Filterbookingamount], -- Use your booking amount measure
DNM_DRILL_THROUGH[FISCAL_QTR] IN { selected_quarter, pqtr, ppqtr },
IF(
DNM_DRILL_THROUGH[FISCAL_QTR] = selected_quarter,
INT(RIGHT(DNM_DRILL_THROUGH[FISCAL_WEEK_NUMBER_D], 2)) <= max_week_selected,
TRUE() -- Ensures previous quarters show all 13 weeks
)
)
Best regards,
Ray Minds
http://www.rayminds.com
https://www.linkedin.com/company/rayminds/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.