The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I created two measures for my accumulated graphic that today is showing 2022x2021, one measure is calculating the sum of 2022 and the other is calculating the sum of 2021.
I need to improve the measure to use the filter of the year to change the comparison for 2023x2022, but without losing the previous view. I want to use a measure that I can use only de filter of the year and get the comparison of the filtered year with the previous year.
Follow the example below:
Thanks in advance
Solved! Go to Solution.
For the current year
Accumulated Packs =
CALCULATE(SUM(FactTable[MeasureName]))
For the previous year:
Accumulated Packs Previous Year =
CALCULATE(SUM(FactTable[MeasureName]) , PREVIOUSYEAR('DimCalendar'[DateKey]))
Alternate
Accumulated Packs Previous Year =
CALCULATE(SUM(FactTable[MeasureName]) , SAMEPERIODLASTYEAR('DimCalendar'[DateKey]))
Hi @VitorXavierCell
Please refr to attached sample file. I hope it will help you find the workable solution for your case.
Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
Sales Amount RT =
CALCULATE (
[Sales Amount],
'Date'[Date] <= MAX ( 'Date'[Date] )
)
Sales Amount RT Selected Year =
VAR SelectedYear = MAX ( Years[Year] )
VAR StartDate =
CALCULATE (
MIN ( 'Date'[Date] ),
'Date'[Year] = SelectedYear,
ALL ( 'Date' )
)
RETURN
CALCULATE (
[Sales Amount RT],
'Date'[Year] = SelectedYear,
Sales[Order Date] >= StartDate
)
Sales Amount RT Previous Year =
VAR SelectedYear = MAX ( Years[Year] )
VAR StartDate =
CALCULATE (
MIN ( 'Date'[Date] ),
'Date'[Year] = SelectedYear - 1,
ALL ( 'Date' )
)
RETURN
CALCULATE (
[Sales Amount RT],
'Date'[Year] = SelectedYear - 1,
Sales[Order Date] >= StartDate
)
Are you hardcoding the Year filter in the Measures?
If yes, then just remove the year filter and then you can use just 1 measure and then in the Visual, throw Year as a Legend.
Hello @Anonymous
Thank you for your answer!
Your solution works in a good way, but I'd like to know if it's possible when I filter the year 2022 it shows the 2022 x 2021, and when I select the year 2023 it shows the comparison 2023x2022 in the graphic. Is that possible?
Thanks a lot
For the current year
Accumulated Packs =
CALCULATE(SUM(FactTable[MeasureName]))
For the previous year:
Accumulated Packs Previous Year =
CALCULATE(SUM(FactTable[MeasureName]) , PREVIOUSYEAR('DimCalendar'[DateKey]))
Alternate
Accumulated Packs Previous Year =
CALCULATE(SUM(FactTable[MeasureName]) , SAMEPERIODLASTYEAR('DimCalendar'[DateKey]))
Sure, create a new measure referencing old measure with Sameperiodlastyear filter. Then select it in the Values.
please mark my answer as a solution if it worked.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
57 | |
32 | |
20 | |
17 | |
15 |
User | Count |
---|---|
53 | |
31 | |
30 | |
23 | |
21 |