Forum Discussion

Power_steffi's avatar
Power_steffi
Regular Visitor
3 years ago

Previous Year empty values

I'm trying to calculate the sales for the previous year in Power BI using DAX formulas, but all the formulas that I've tried are giving me blank values. I wanted to calculate the growth but I realized just the previous year, already give me empty values. 

 

Sales Previous Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(raw[Inv. Date]))

Sales Previous Year = CALCULATE([Total Sales], FILTER(raw, raw[Year] = MAX(raw[Year])-1 && raw[Quarter] = MAX(raw[Quarter]) && raw[Month] = MAX(raw[Month])))

Sales Previous Year = CALCULATE([Total Sales], DATEADD(raw[Inv. Date], -1, YEAR))

Sales Previous Year = CALCULATE([Total Sales], PREVIOUSYEAR(raw[Inv. Date]))

% Change = 
VAR CurrentValue = raw[Total Sales]
VAR PREviousValue = 
    SWITCH(
        TRUE(),
        ISINSCOPE(raw[Inv. Date].[Date]), 
            CALCULATE([Total Sales], 
                DATEADD(raw[Inv. Date], -1, DAY)
            ),
        ISINSCOPE(raw[Inv. Date].[Monat]), 
            CALCULATE([Total Sales], 
                PARALLELPERIOD(raw[Inv. Date], -1, YEAR)
            ),
        ISINSCOPE(raw[Inv. Date].[Quartal]), 
            CALCULATE([Total Sales], 
                PARALLELPERIOD(raw[Inv. Date], -1, YEAR)
            ),
        ISINSCOPE(raw[Inv. Date].[Jahr]), 
            CALCULATE([Total Sales], 
                DATEADD(raw[Inv. Date], -1, YEAR)
            )
    )
RETURN
    IF(PREviousValue = 0, BLANK(), DIVIDE(CurrentValue - PREviousValue, PREviousValue))

 What I wanted to see is for instance for 2022 Qrts 2 - 3754.62.

 

However, all these formulas return blank values, even though there is data available for the previous year.

 

Also the date is correcly transformed. 

Following other post, I could not find the solution for my problem. 

 

 

Can anyone suggest what might be causing this issue and how I can fix it? Any help would be greatly appreciated.

 

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Power_steffi ,

     

    I think you can try ALL() function in your calculation. Your measure should look like as below.

    Sales Previous Year =
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( raw ),
            raw[Year]
                = MAX ( raw[Year] ) - 1
                && raw[Quarter] = MAX ( raw[Quarter] )
                && raw[Month] = MAX ( raw[Month] )
        )
    )

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.