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.
Hi All,
Amount last month vs previous month.
Dear all i would need your support again:
I would need a measure which calculates last month amount (updates each month a new column is avaialble) - previous month amount:
in this case amount of 12/1/2017 - amount of 12/1/2017
if i have an update for 01/01/2018 than amount of 01/01/2018- amount of 12/01/2017..
is it possible ?
Many Thanks
Solved! Go to Solution.
Hi @alexbalazsalex,
Please unpivot table structure in Query Editor mode to get below structure.
Then, create a calculated column to get the difference of amount between last month and previous month
Amount last month vs previous month = CALCULATE ( SUM ( Test3[Value] ), FILTER ( ALLEXCEPT ( Test3, Test3[Category] ), IF ( MONTH ( TODAY () ) = 1, Test3[Date].[Year] = YEAR ( TODAY () ) - 1 && Test3[Date].[MonthNo] = 12, Test3[Date].[MonthNo] = MONTH ( TODAY () ) - 1 ) ) ) - CALCULATE ( SUM ( Test3[Value] ), FILTER ( ALLEXCEPT ( Test3, Test3[Category] ), IF ( MONTH ( TODAY () ) = 1, Test3[Date].[Year] = YEAR ( TODAY () ) - 1 && Test3[Date].[MonthNo] = 11, IF ( MONTH ( TODAY () ) = 2, Test3[Date].[Year] = YEAR ( TODAY () ) - 1 && Test3[Date].[MonthNo] = 12, Test3[Date].[MonthNo] = MONTH ( TODAY () ) - 2 ) ) ) )
Based on above table, create a new table.
Test4 = UNION ( SELECTCOLUMNS ( Test3, "Category", Test3[Category], "Date", Test3[Date], "Amount", Test3[Value] ), SELECTCOLUMNS ( SUMMARIZE ( Test3, Test3[Category], "Diff", AVERAGE ( Test3[Amount last month vs previous month] ) ), "Category", [Category], "Date", "Amount last month vs previous month", "Amount", [Diff] ) )
Use a matrix visual to display corresponding fields in Test4.
Each month the source data is updated, when you refresh the report page manually, the data displayed on visual will be updated, too.
Regards,
Yuliana Gu
Hi @alexbalazsalex,
Please unpivot table structure in Query Editor mode to get below structure.
Then, create a calculated column to get the difference of amount between last month and previous month
Amount last month vs previous month = CALCULATE ( SUM ( Test3[Value] ), FILTER ( ALLEXCEPT ( Test3, Test3[Category] ), IF ( MONTH ( TODAY () ) = 1, Test3[Date].[Year] = YEAR ( TODAY () ) - 1 && Test3[Date].[MonthNo] = 12, Test3[Date].[MonthNo] = MONTH ( TODAY () ) - 1 ) ) ) - CALCULATE ( SUM ( Test3[Value] ), FILTER ( ALLEXCEPT ( Test3, Test3[Category] ), IF ( MONTH ( TODAY () ) = 1, Test3[Date].[Year] = YEAR ( TODAY () ) - 1 && Test3[Date].[MonthNo] = 11, IF ( MONTH ( TODAY () ) = 2, Test3[Date].[Year] = YEAR ( TODAY () ) - 1 && Test3[Date].[MonthNo] = 12, Test3[Date].[MonthNo] = MONTH ( TODAY () ) - 2 ) ) ) )
Based on above table, create a new table.
Test4 = UNION ( SELECTCOLUMNS ( Test3, "Category", Test3[Category], "Date", Test3[Date], "Amount", Test3[Value] ), SELECTCOLUMNS ( SUMMARIZE ( Test3, Test3[Category], "Diff", AVERAGE ( Test3[Amount last month vs previous month] ) ), "Category", [Category], "Date", "Amount last month vs previous month", "Amount", [Diff] ) )
Use a matrix visual to display corresponding fields in Test4.
Each month the source data is updated, when you refresh the report page manually, the data displayed on visual will be updated, too.
Regards,
Yuliana Gu
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 |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |