Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |