I need to calculate week on week variance for data starting from 2018 till date. Date column includes a day of the week when the data is shared (say, 3/1/2023). Month column is calculated to indicated month of the year (January 2023). Week Rank is assigned to all weeks. The DAX is shared here for your reference and I would need to know how I can make this work.
CALCULATE(
SUM('fred_h8_banking'[Amt in Bn_CF]),
FILTER(
ALL('fred_h8_banking'[Week Rank_CF], 'fred_h8_banking'[series_title], 'fred_h8_banking'[Month], 'fred_h8_banking'[A/L], 'fred_h8_banking'[banks]),
'fred_h8_banking'[Week Rank_CF] = MAX('fred_h8_banking'[Week Rank_CF])
&& 'fred_h8_banking'[series_title] = MAX('fred_h8_banking'[series_title])
&& 'fred_h8_banking'[Month] = MAX('fred_h8_banking'[Month])
&& 'fred_h8_banking'[A/L] = MAX('fred_h8_banking'[A/L])
&& 'fred_h8_banking'[banks] = MAX('fred_h8_banking'[banks])
)
)
VAR SalesLastWeek_CF =
CALCULATE(
SUM('fred_h8_banking'[Amt in Bn_CF]),
FILTER(
ALL('fred_h8_banking'[Week Rank_CF], 'fred_h8_banking'[series_title], 'fred_h8_banking'[Month], 'fred_h8_banking'[A/L], 'fred_h8_banking'[banks]),
'fred_h8_banking'[Week Rank_CF] = MAX('fred_h8_banking'[Week Rank_CF]) - 1
&& 'fred_h8_banking'[series_title] = MAX('fred_h8_banking'[series_title])
&& 'fred_h8_banking'[Month] = MAX('fred_h8_banking'[Month])
&& 'fred_h8_banking'[A/L] = MAX('fred_h8_banking'[A/L])
&& 'fred_h8_banking'[banks] = MAX('fred_h8_banking'[banks])
)
)
RETURN DIVIDE(SalesCurrentWeek_CF, SalesLastWeek_CF, 0)
