How to calculate a measure based on a decreasing/increasing column's value in DAX

1
votes

I have a Sales table and related dimension tables. MySales table contains columns : Week, StoreID, SalesSeasonID, ProductKey and metrics. My dimensions are related to sales table (Date,SalesSeason,Store,Product tables).

I need to find Sales Quantity (LastYear and LastSeason), as a measure You can find a sample below:

enter image description here

My purpose is when user selected SaleseasonID[4] then it will return 2 as SalesQuantity.

How can I calculate this measure by DAX formula?

1
daxtabular

1 Answers

1
votes

Try:

PYSales = 
SUMX ( 
    VALUES ( Table1[YearWeek] ),
    CALCULATE ( 
        SUM ( Table1[SalesQuantity] ),
        ALL ( Table1[SalesSeasonID] ), 
        FILTER ( 
            ALL ( Table1[YearWeek] ),
            Table1[YearWeek] = EARLIER ( Table1[YearWeek] ) - 100
        )
    )
)

Worked example PBIX file, using your sample data: https://pwrbi.com/so_55703551/

enter image description here