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
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'.