Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |