Forum Discussion
Previous_Value_as per Day_range
Prev_Value =
VAR CurrentYear = SELECTEDVALUE('YourTable'[Year])
VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month_Number])
VAR CurrentCustom = SELECTEDVALUE('YourTable'[Custom])RETURN
CALCULATE(
[Avg_Value],
TOPN(
1,
FILTER(
ALLSELECTED('YourTable'),
('YourTable'[Year] = CurrentYear && 'YourTable'[Month_Number] = CurrentMonth && 'YourTable'[Custom] < CurrentCustom) ||
('YourTable'[Year] = CurrentYear && 'YourTable'[Month_Number] < CurrentMonth) ||
('YourTable'[Year] < CurrentYear)
),
'YourTable'[Year], DESC,
'YourTable'[Month_Number], DESC,
'YourTable'[Custom], DESC
)
)
Hiii MHTANK
Here is the modified DAX measure
Prev_Value =
VAR CurrentYear = SELECTEDVALUE('YourTable'[Year])
VAR CurrentMonth = VALUE(SELECTEDVALUE('YourTable'[Month])) -- Convert to number
VAR CurrentDayRange = VALUE(SELECTEDVALUE('YourTable'[Day_Range])) -- Convert to number
RETURN
CALCULATE(
[Avg_Value],
FILTER(
ALL('YourTable'),
('YourTable'[Year] = CurrentYear && VALUE('YourTable'[Month]) = CurrentMonth && VALUE('YourTable'[Day_Range]) < CurrentDayRange) ||
('YourTable'[Year] = CurrentYear && VALUE('YourTable'[Month]) < CurrentMonth) ||
('YourTable'[Year] < CurrentYear)
),
LASTNONBLANK('YourTable'[Day_Range], [Avg_Value])
)
If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
This give me error. cannot convert "Jan" of type text to type number.
Create a Numeric Month calculated Column
Create a calculated column in your table to convert month names into numbers
Month_Number =
SWITCH(
'YourTable'[Month],
"Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6,
"Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12,
BLANK()
)Modify Your Measure to Use Month_Number
Prev_Value =
VAR CurrentYear = SELECTEDVALUE('YourTable'[Year])
VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month_Number]) -- Use the numeric month column
VAR CurrentDayRange = VALUE(SELECTEDVALUE('YourTable'[Day_Range])) -- Ensure numeric comparisonRETURN
CALCULATE(
[Avg_Value],
FILTER(
ALL('YourTable'),
('YourTable'[Year] = CurrentYear && 'YourTable'[Month_Number] = CurrentMonth && VALUE('YourTable'[Day_Range]) < CurrentDayRange) ||
('YourTable'[Year] = CurrentYear && 'YourTable'[Month_Number] < CurrentMonth) ||
('YourTable'[Year] < CurrentYear)
),
LASTNONBLANK('YourTable'[Day_Range], [Avg_Value])
)not working
not getting outputyou getting any error?