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.
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.
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 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |