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 ca...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft 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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Phil_Seamark nothing changes.

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft 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)
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Phil_Seamark

         

        [Year-Week] and [LastYear- Week] are columns not measures.

        [LastYearRetailSales] is also a column

         

        Year-Week = CONCATENATE(CONCATENATE([Year],"-"),[WeekNum])

        WeekNum = FORMAT([Week], "00")

         

        LastYear-Week = CONCATENATE(CONCATENATE([PreviousYear],"-"),FORMAT([Week], "00"))

        PreviousYear = [Year] -1

         

        [Year] and [Week] are both whole numbers datatypes.

         

        If I add the measure to my model [LastYearRetailSales TEST] I get the error

         

        The value for 'Year-Week' cannot be determined. Either 'Year-Week' doesn't exist, or there is no current row for a column named 'Year-Week'.