Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I create a "total operating result" report for the customer by individual months and years.
I have gone through several tutorials that addressed the issue at hand but have not achieved a satisfactory result.
To simplify the assignment, I created sample data, which I will use to explain the needs.
Measures:
SumPriceWithoutVAT Year1 = CALCULATE([SumPriceWithoutVAT], CalendarTable_NEW[Date].[Year] = MIN(CalendarTable_NEW[Date].[Year]))
SumPriceWithoutVAT Year2 = CALCULATE([SumPriceWithoutVAT], CalendarTable_NEW[Date].[Year] = MAX(CalendarTable_NEW[Date].[Year]))
Interannual Difference_V3 =
VAR Year1 = CALCULATE( [Sum_PriceWithoutVAT],Interannual Difference =
VAR SelectedYears = VALUES(Calendar_Table_NEW[YEAR])
VAR Year1 = MINX(SelectedYears, Calendar_Table_NEW[YEAR]) -- Select year 1 by slicer
VAR Year2 = MAXX(SelectedYears, Calendar_Table_NEW[YEAR] ) -- Select year 2 by slicer
VAR TotalYear1 =
CALCULATE([SumPriceWithoutVAT], 'Calendar_Table_NEW'[Year] = Year1)
VAR TotalYear2 =
CALCULATE([SumPriceWithoutVAT], 'Calendar_Table_NEW'[Year] = Year2)
RETURN
SumYear2 - SumYear1
calculated_year-to-year_difference_in_total_V2 = [SumPriceWithoutVAT Year2] - [SumPriceWithoutVAT Year1]
Could you please advise me where I might be making a mistake? Why doesn't the matrix behave "similarly" to the table - considering the selection of years from the slicer?
I would be very grateful for any advice on how I can do calculations while the user selects the year according to his/her own wishes
Thanks a lot Tom.
Solved! Go to Solution.
Hi, @TomasGazsi
Thanks for reaching out to the Microsoft fabric community forum.
The reason for this issue is that in the matrix, you have set the column to year, so your output results include a year filter. This causes the variable filter in the measure to be empty. In your table visual object, there is no year, so the output is normal. My suggestion is to create an unrelated date table to use as the rows and columns in the matrix, and replace the values part with measures:
1.First, create a calculated table, then add it to the matrix, using the year from the calculated table as a slicer:
Table = CALENDAR(DATE(2023, 01, 01), DATE(2026, 12, 31))
2.Then, use the following two measures as values:
Normal total =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
diff =
VAR mid1 =
MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mad1 =
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mid2 =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
VAR mad2 =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
VAR count1 =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Date].[Year] ), ALLSELECTED ( 'Table' ) )
VAR f =
IF ( count1 = 2, mad2 - mid2, BLANK () )
RETURN
f
3.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-linyulu-msft Thank you so much!!
It helped me solve my problem that I had been struggling with for several days.
Hi, @TomasGazsi
Thanks for reaching out to the Microsoft fabric community forum.
The reason for this issue is that in the matrix, you have set the column to year, so your output results include a year filter. This causes the variable filter in the measure to be empty. In your table visual object, there is no year, so the output is normal. My suggestion is to create an unrelated date table to use as the rows and columns in the matrix, and replace the values part with measures:
1.First, create a calculated table, then add it to the matrix, using the year from the calculated table as a slicer:
Table = CALENDAR(DATE(2023, 01, 01), DATE(2026, 12, 31))
2.Then, use the following two measures as values:
Normal total =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
diff =
VAR mid1 =
MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mad1 =
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mid2 =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
VAR mad2 =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
VAR count1 =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Date].[Year] ), ALLSELECTED ( 'Table' ) )
VAR f =
IF ( count1 = 2, mad2 - mid2, BLANK () )
RETURN
f
3.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-linyulu-msft And I see that the TOTAL row is average, but can I change it to SUM the whole year? Thanks
Hi, @TomasGazsi
Thank you for your prompt response and for accepting my idea as the solution. Regarding your question, you just need to add an additional check using ISINSCOPE(). I have modified the measure as follows:
diff =
VAR mid1 =
MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mad1 =
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mid2 =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
VAR mad2 =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
VAR count1 =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Date].[Year] ), ALLSELECTED ( 'Table' ) )
RETURN
IF (
count1 = 2,
IF (
ISINSCOPE ( 'Table'[Date].[Month] ),
mad2 - mid2,
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
)
)
- CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
)
)
),
BLANK ()
)
Normal total =
VAR ff =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
RETURN
IF (
ISINSCOPE ( 'Table'[Date].[Month] ),
ff,
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
)
)
)
Here is the current result:
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 |