Forum Discussion

EitanKGCS's avatar
EitanKGCS
Frequent Visitor
3 years ago
Solved

Measure not producing a column subtotal

Hi all,

 

I have a measure that pulls sales from the same period last year. I can't use SAMEPERIODLASTYEAR as the fact table is a non-standard date table (my fact table only has week-ending dates, so the same week-ending date last year is always 1 day ahead) so I wrote this instead:

 

Prior year = VAR CurrentMonth = SELECTEDVALUE( Dates[Month Name] ) VAR CurrentYear = SELECTEDVALUE( Dates[Year] ) RETURN CALCULATE( sumx('All Companies','All Companies'[@Profit]), FILTER( ALL( Dates ), Dates[Month Name] = CurrentMonth && Dates[Year] = CurrentYear - 1))​

 This works other than the fact that it doesn't show column subtotals for some reason. Does anyone have any ideas on how I can fix this?

 

 
I have tried other solutions as well which also don't work, for example:
 
 

 

if(HASONEVALUE(Dates[Fiscal Month]),CALCULATE( [Total GP],
    FILTER( ALL( Dates ),
		Dates[Month Name] = CurrentMonth && Dates[Year] = CurrentYear - 1)),SUMX(VALUES(Dates[Fiscal Month]),CALCULATE( [Total GP],
    FILTER( ALL( Dates ),
		Dates[Month Name] = CurrentMonth && Dates[Year] = CurrentYear - 1))

 

 

 
 

  

Thanks!

 
@Dax Measure Error
  • amitchandak's avatar
    amitchandak
    3 years ago

    EitanKGCS , Try new measure like

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

     

    That should give a year behind total

4 Replies