DAX formula for variance

[Anonymous]

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

[Phil_Seamark]

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]

@Phil_Seamark nothing changes.

[Phil_Seamark]

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]

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