Forum Discussion

DataSundowner's avatar
DataSundowner
Icon for Helper II rankHelper II
3 years ago
Solved

DAX to Calculate Same Week Prior Year

Hi Everyone. I'm trying to calculate what sales were for each week prior year. I have created a measure looks like below. It works fine to calcualte for each week. However, it stops calculating the total when the date range corsses more than one year. How can I fix this? Thanks in advance. 

 

Amt Prior Yr = 
IF (
    HASONEVALUE ( DimDate[ISO_Year] ),
    CALCULATE (
        SUM(SalesFact[Amt]),
        FILTER (
            ALL ( DimDate ),
            DimDate[ISO_Year] = VALUES ( DimDate[ISO_Year] ) - 1
                && CONTAINS(
                    VALUES ( DimDate[ISO_WeekNum] ),
                     DimDate[ISO_WeekNum],
                     DimDate[ISO_WeekNum] )
        )
    ),
    BLANK()
)

 

 

Total line works fine if in the same year:

 

Total line stops working when there are multiple years:

 

2 Replies