Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

DAX formula for variance

I have a table with sales and dates which are data type text.

What I am trying to work out is the variance from the retail sales data from the previous year.

 

I have a DAX formula which nearly calculates it

 

LastYearRetailSales =
var previous = [LastYear-Week]
var saleonpre = CALCULATE(SUM(MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES[Retail Sales]),ALL(MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES),MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES[Year-Week] = previous)
return
IF(ISBLANK(saleonpre),0,saleonpre)

 

The problem is LastYearRetailSales are vastly overstated. What am I doing wrong?

 

 

I basically want to know the variance between the two lines when I have Year as a Legend

 

 

Need Help

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI Anonymous

     

    What happens when you try

     

    LastYearRetailSales =
var previous = [LastYear-Week]
var saleonpre = CALCULATE(SUM(MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES[Retail Sales]),ALLSELECTED(MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES),MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES[Year-Week] = previous)
return
IF(ISBLANK(saleonpre),0,saleonpre)
      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        HI Anonymous

         

        You must have a filter somewhere that we can't see in your existing information

         

        What happens when you add the following measure to your model.

         

        Oh and are the [Year-Week] and [LastYear-Week] calculated measures?  or are they text fields?

         

        LastYearRetailSales TEST =
var previous = [Year-Week]
var saleonpre = CALCULATE(SUM(MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES[Retail Sales]),ALL(MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES),MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES[Year-Week] = previous)
return
IF(ISBLANK(saleonpre),0,saleonpre)