Forum Discussion
MAli_Ch
2 years agoRegular Visitor
DAX changing its value
Hi, I'm working on a report where I have to show quarterly data based upon one or multiple years selected from the filter. I want to store the smallest year value for which i have created a measure shared below. Lets say I select year 2024,2025 and 2026 from filter. Now I should get cumulative sum of each quarter with its previous year. If I hardcode year in my dax then it works fine otherwise it cumulate sum for each years individually which I dont want. below are the measures and screenshot of line chart with hardcode value and with measure's value.
(Measure to get smallest year)
MinYearSelected =
VAR SelectedYears =
VALUES('DIM_DATE'[REPORT_YEAR])
RETURN
MINX(SelectedYears, 'DIM_DATE'[REPORT_YEAR])
(Main Measure)
Cumulative Measure Sum =
var min_year = [MinYearSelected]
RETURN
CALCULATE(
[udp high total fcst],
FILTER(
ALLSELECTED('DIM_DATE'[WEEK_END_DATE]),
AND(
ISONORAFTER('DIM_DATE'[WEEK_END_DATE], MAX('DIM_DATE'[WEEK_END_DATE]), DESC),
YEAR('DIM_DATE'[WEEK_END_DATE]) >= min_year
)
))
(Result I want using variable having smallest year value)
(Result I want using variable having smallest year value)
(Result I am getting using variable having smallest year value)
Hi MAli_Ch
please try
MinYearSelected =
MINX ( ALLSELECTED ( 'DIM_DATE'[REPORT_YEAR] ), 'DIM_DATE'[REPORT_YEAR] )