Same time last year - Display only past 6 months based on Year Month selection

DAX DAX Calculations
1

Hi All,

I am working on a scenario wherein I need to display the values of same time last year(actually -11 month value) for the current month year selection. If nothing is selected in the slicer it should display the STLY values for past 6 months from the Max Sale date.

i.e. if I select 201912 then it should display sales of 201901 for 201912 and 201812 sale for 201911 so on.

I did achieve this using a disconnected table logic , but I require your help to see whether same can be achieved using without using disconnected table and a simpler logic.

Attached is the screen shot and the pbix of implementation done by me

Capture
Capture822×547 26.4 KB
Dynamic STLY .pbix (489.6 KB)

2

Hi @Vishy,

Without the disconnected Date table a Slicer selection would result in just one row from the Date table. Don’t see a way around that.

Here’s a measure for calculating last 6M Sales.

Sales Last 6M = 
VAR LastMonthInYear = SELECTEDVALUE( Disconnected[MonthnYear] )
VAR LastSale = MAXX( ALL( Sales[Sales Date]), Sales[Sales Date] )
VAR MonthNum = COALESCE( LOOKUPVALUE( Dates[MonthIndex1], Dates[MonthnYear], LastMonthInYear ), LOOKUPVALUE( Dates[MonthIndex1], Dates[Date], LastSale ) )
RETURN

SUMX(
    CALCULATETABLE( VALUES( Dates[MonthYear] ),
        FILTER( ALL( Dates ), Dates[MonthIndex1] >= MonthNum -5 && Dates[MonthIndex1] <= MonthNum )
    ), [Total Sales]
)
1 Like
3

Thanks Melissa - I always learn something new from you guys. Thanks so much.

4

Glad I could help.
:+1: