Cumulative weekly sales measure


#1

Hello Sam,

I have used a weekly dax measure which you have introduced in one of the Youtube video as follow:-

Cumulative LYYTD-custom Wkly Sales = 
Var Currentweek= SELECTEDVALUE('Date'[New Week Num])
Var Currentyear= SELECTEDVALUE('Date'[Year-Thurs])

Return
calculate([Total sale],
       filter(all('Date'),
         'Date'[New Week Num] = Currentweek &&
         'Date'[Year-Thurs] = Currentyear - 1 ))

However, when I dragged this measure to canvas, cant display visual and the error message as follow:-

Error Message:
MdxScript(Model) (100, 10) Calculation error in measure ‘Key Measure’[Cumulative LYYTD-custom Wkly Sales]: DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

Please advise what went wrong and how to correct the measure. Thanks.


#2

Convert your year column in your date table to a whole number data type. That will do it.


#3

Hopefully this was sorted for you. Let me know if now.

Side note for inputting formula into forum. See here - How Place DAX Formula Into Forum Topics & Posts


#4

Thanks. I got it.

I wrote a measure for the growth of cumulative sales, ie cumulative sales TYYTD weekly sales - cumulative LYYTD weekly sales. When I displayed the result in table, it is fine. However, if I displayed the result in Card, it turned out blank as follow:-

Inline image

My data model is Date in date table is linked to date in sales table. Do I need to build a relationship between New Week Num in date table to date in sales table in order for the result to be displayed in Card? Please advise. Thanks.


#5

No you don’t need any extra relationships.

Have you reviewed this videos here, I’ve look to create a number of these to assist with problems on totals


#6

Yes. Please advise how to incorporate the measure of cumulative weekly sales (listed below) into the measure which shown in the video.

Cumulative weekly sales measure :-
Var Currentweek= SELECTEDVALUE(‘Date’[New Week Num])
Var Currentyear= SELECTEDVALUE(‘Date’[Year-Thurs])

Return
calculate([Total sale],
       filter(all('Date'),
         'Date'[New Week Num]= Currentweek &&
         'Date'[Year-Thurs]=Currentyear ))

Measure shown in video:

Monthly Averages=
Var MonthlyAverage = AVERAGEX(VALUES(Dates[Month & Year]), [total Sales])
Var AverageTotal = Summarize(Dates, Dates[MonthName], "Monthly Avg", MonthlyAverage)

Return
If(HASONEVALUE(dates[MonthName]),
  MonthlyAverage,
     Sumx(averageTotal, [Monthly Avg])

#7

Can you please send me your model, I can’t replicate exactly what you need in my own datasets. Thanks


#8

This is the data model

Capture


#9

Try this patterns here

Total Sales YTD - Custom 2 = 
VAR CurrentFinWeek = MAX( 'Calendar Daily'[Fin Week Number] )
VAR CurrentFinYear = MAX( 'Calendar Daily'[Fin Year] )

RETURN
SUMX(
	FILTER( 
        SUMMARIZE( ALL( 'Calendar Daily' ), 'Calendar Daily'[Fin Year], 'Calendar Daily'[Fin Week Number],
        "Sales", [Total Sales] ) ,
		'Calendar Daily'[Fin Year] = CurrentFinYear &&
		'Calendar Daily'[Fin Week Number] <= CurrentFinWeek ), 
    [Sales] )

Here’s an entire video on this.

You may need to make slight adjustments but this explains everything.

Thanks