Measure not producing a column subtotal

[EitanKGCS]

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!

[amitchandak · Accepted solution]

@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

[EitanKGCS]

@amitchandak thank you for the idea but it hasn't worked. 

I tried this: 

CALCULATE(SUM('All Companies'[@Profit]),dateadd('Dates'[Date],-1,Year))

But I got the following error:

Function 'DATEADD' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship.

Any suggestions on how to fix this error?

[amitchandak]

@EitanKGCS , Date table should have all dates(Marked as date table) , You join should be single directional

few other reasons

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[EitanKGCS]

@amitchandak I see! I added in your formula and changed the date table to single and it works! I have to reorganise most of my filter slicers and other filters but at least I'm getting correct figures now. 

Thanks so much!