Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
How can I calculate the "Value Begin" measure when it always has the same value as the "Final Value" of the previous week of analysis.
In this example the "Final value" is 80,30,30 for the analysis week 26 Dec to 01 Jan and for categories x,y,z. So the "Value Begin" for the following analysis week (02 Jan to 08 Jan) should be 80,30,30 for category x,y,z.
How to do this in Dax?
I tried with OFFSET
CALCULATE([Final Value],
OFFSET(
-1,
ALLSELECTED(d_calendar[Analyze week]),
GROUPBY(d_calendar[Analyze week]))
)
but doesnt work, returned blank
Solved! Go to Solution.
I solved.
it is necessary to create a classifier, for using OFFSET in the ORDERBY, I concatenated the year of the end of the week from the column date with the week of the year of the end of the week from the column date and then used offset:
CALCULATE([Final Value],
OFFSET(
-1,
ALLSELECTED(
'd_Calendar'[Concat(Year-Week(EndofWeek)],
'd_Calendar'[Week analyze]),
ORDERBY('d_Calendar'[Concat (Year-Week(EndofWeek)]
)
)
d_calendar below:
I solved.
it is necessary to create a classifier, for using OFFSET in the ORDERBY, I concatenated the year of the end of the week from the column date with the week of the year of the end of the week from the column date and then used offset:
CALCULATE([Final Value],
OFFSET(
-1,
ALLSELECTED(
'd_Calendar'[Concat(Year-Week(EndofWeek)],
'd_Calendar'[Week analyze]),
ORDERBY('d_Calendar'[Concat (Year-Week(EndofWeek)]
)
)
d_calendar below: