DAX that works with/rolls up to different dimensions

[Anonymous]

Hello All,

I have had a look for this topic and can't find any discussion about it so i'm either the first to ask or my search paremeters are shocking.

Either way, I need some help! I have a requirement to compare a sum total to the same day in the previous week to provide a week on week variance. This is an amendment to an existing report in my business that was originaly aggregating numbers per week. Now the requirements is to look at these figures each day of the week but it still needs to be aggreagated for the week. This is where i am struggling as I can either get it to work for each day or for the week as a total. I've tried simply using DATEADD but this always results in an error (returns multiple results when it expects one). 

My source data is an aggregated view that appears as follows:

This is linked with a DIM_DATE table with many columns but the ones used in my report are the Calendar_Date (for the relationship to the KPIs table shown above), [Start of Week], [FISCAL_WEEK], [WEEK_DAY_NAME]. 

The normal SUM DAX for the Gross Orders per day is fine (as you'd expect), but my WOW% comparison is being a pain:

My current DAX is as follows:

Orders WOW Growth =

VAR SumOrders =

SUMX ( SALES_KPIS, SALES_KPIS[GrossOrders] )

VAR PreviousFiscalWeek =

MAX ( DIM_DATE[FISCAL_WEEK] ) - 1

VAR WeekDayName =

SELECTEDVALUE ( DIM_DATE[WEEK_DAY_NAME] )

VAR OrdersPreviousWeekDay =

CALCULATE (

SUM ( SALES_KPIS[GrossOrders] ),

FILTER ( ALL ( DIM_DATE ), DIM_DATE[FISCAL_WEEK] = PreviousFiscalWeek ),

DIM_DATE[WEEK_DAY_NAME] = WeekDayName

)

VAR OrdersPreviousWeek =

CALCULATE (

SUM ( SALES_KPIS[GrossOrders] ),

FILTER ( ALL ( DIM_DATE ), DIM_DATE[FISCAL_WEEK] = PreviousFiscalWeek )

)

VAR GrowthPercentage =

IFERROR (

( SumOrders - OrdersPreviousWeekDay ) / OrdersPreviousWeekDay,

BLANK ()

)

RETURN

GrowthPercentage

If i swap out the variable 'OrdersPerviousWeekDay' with 'OrdersPreviousWeek', i do get the figure for the week but it also gives me the weekly total for each day which is not what i need! Is there anybody on here that can help point me in the right direction. I've tried using a combination of SWITCH and IF commands as well but return errors each time! I've been trying to get this to work for a day and its now time to raise the white flag. Any help will be appreciated!

Thanks,

Chris 

[Anonymous · Accepted solution]

Update - i retried DATEDIFF this morning and it suddenly works the way I need it to. Very odd, i must have used an incorrect column in my original version of the DATEDIFF formula and not realised. 

[Anonymous]

Orders WOW Growth =

VAR SumOrders = SUM ( SALES_KPIS[GrossOrders] )

VAR SumLastWeek = CALCULATE(SUM(SALES_KPIS[GrossOrders]),DATEADD(DIM_DATE[CALENDAR_DATE],-7,DAY))

RETURN

(SumLastWeek-SumOrders)/SumLastWeek