How to calculate sales value when none of the month is selected and lag value is selected?

[Likhitha]

Hello All,

I'm having sales column.I want to calculate sales based on lag and month selection.I'm having 4 lags(1,2,3,4).

For example ,lag 1 means if my selected month is december then lag month should be november.

I created DAX for that logic and i got the solution.But the problem is if don't select any month and if selected lag,I want the  Output which is shown in the below image.(Lag selection is 1 and no month selection ,so my sales data has to be from Jan-Nov)

DAX:

Lag sales =
var currentmonth=MONTH(MAX(DateTable[Date]))
var Previousmonth= currentdate-SELECTEDVALUE(NewData[Month Lag Number])
var result=CALCULATE(SUM(NewData[Sales]),FILTER(NewData,MONTH(NewData[Date])=Previousmonth))
return
result

[v-juanli-msft · Accepted solution]

Hi @Likhitha 

Create two tables, "lag table" and "month list", they have no relationship with other tables.

Then create a measure

lag sales =
VAR selected_m =
    SELECTEDVALUE ( 'month list'[month list] )
VAR selected_lag =
    SELECTEDVALUE ( 'lag table'[lag] )
VAR lagsales1 =
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER ( 'Table', MONTH ( [date] ) = selected_m - selected_lag )
    )
VAR lagsales2 =
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER ( 'Table', MONTH ( [date] ) <= ( 12 - selected_lag ) )
    )
RETURN
    IF ( ISFILTERED ( 'month list'[month list] ), lagsales1, lagsales2 )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Likhitha]

@v-juanli-msft  Absolutely It's Working perfectly...Thanks a lot....Tank you very very much 🙂