Forum Discussion
Additional Single Column required for Matrix
Good Morning,
I have a Matrix which shows figures for several months:
I wish to have an additional column inserted between the last month in the series column (i.e. July 2024) and the Total column, which shows the difference between the last two months (i.e. June 2024 and July 2024). So in the above example the "theorized" single column should display - 45.7%.
I tried making a measure and adding it to the Values section,
But what happens is it adds a new column (for that measure) for each month (which is not what was intended i.e a single new column calculating the difference only between the two latest months).
Kind Regards
Hi F_Reh
To insert a column between Jul-24 and the Total column, you need a disconnected table with a column for the month-year, the desired name for the difference column, and a custom sort value. Additionally, you'll need to create a measure that references the values in this column and includes logic to calculate the difference between the latest and previous month.
Period With Difference Measure = -- Calculate revenue for the selected period VAR _PeriodValue = CALCULATE ( [Total Revenue], -- Map the selected period to the Dates table using TREATAS TREATAS ( VALUES ( PeriodWithDifference[Period] ), Dates[Month and Year] ) ) -- Get the latest period in the current context as a formatted string (e.g., "Jan-24") VAR _LatestPeriod = FORMAT ( [Latest Date with Data in the current context], "mmm-yy" ) -- Get the previous period by shifting the latest date back by one month VAR _PrevPeriod = FORMAT ( EDATE ( [Latest Date with Data in the current context], -1 ), "mmm-yy" ) -- Calculate the difference in revenue between the latest and previous periods VAR _Diff = CALCULATE ( [Total Revenue], -- Retrieve revenue for the latest period FILTER ( ALL ( Dates ), Dates[Month and Year] = _LatestPeriod ) ) - CALCULATE ( [Total Revenue], -- Retrieve revenue for the previous period FILTER ( ALL ( Dates ), Dates[Month and Year] = _PrevPeriod ) ) -- Return the difference if "Difference" is selected, otherwise return the period revenue RETURN IF ( SELECTEDVALUE ( PeriodWithDifference[Period] ) = "Difference", _Diff, _PeriodValue )Please see the attached sample pbix.
4 Replies
Diff_Last_Two_Months = VAR LastMonth = MAX('Table'[Month]) VAR SecondLastMonth = CALCULATE(MAX('Table'[Month]), 'Table'[Month] < LastMonth) VAR LastMonthValue = CALCULATE(SUM('Table'[Value]), 'Table'[Month] = LastMonth) VAR SecondLastMonthValue = CALCULATE(SUM('Table'[Value]), 'Table'[Month] = SecondLastMonth) RETURN IF(NOT ISBLANK(SecondLastMonthValue), DIVIDE(LastMonthValue - SecondLastMonthValue, SecondLastMonthValue), BLANK() ) // and then convert this measure to Percentage & add it to matrix on your desire placeHi F_Reh
To insert a column between Jul-24 and the Total column, you need a disconnected table with a column for the month-year, the desired name for the difference column, and a custom sort value. Additionally, you'll need to create a measure that references the values in this column and includes logic to calculate the difference between the latest and previous month.
Period With Difference Measure = -- Calculate revenue for the selected period VAR _PeriodValue = CALCULATE ( [Total Revenue], -- Map the selected period to the Dates table using TREATAS TREATAS ( VALUES ( PeriodWithDifference[Period] ), Dates[Month and Year] ) ) -- Get the latest period in the current context as a formatted string (e.g., "Jan-24") VAR _LatestPeriod = FORMAT ( [Latest Date with Data in the current context], "mmm-yy" ) -- Get the previous period by shifting the latest date back by one month VAR _PrevPeriod = FORMAT ( EDATE ( [Latest Date with Data in the current context], -1 ), "mmm-yy" ) -- Calculate the difference in revenue between the latest and previous periods VAR _Diff = CALCULATE ( [Total Revenue], -- Retrieve revenue for the latest period FILTER ( ALL ( Dates ), Dates[Month and Year] = _LatestPeriod ) ) - CALCULATE ( [Total Revenue], -- Retrieve revenue for the previous period FILTER ( ALL ( Dates ), Dates[Month and Year] = _PrevPeriod ) ) -- Return the difference if "Difference" is selected, otherwise return the period revenue RETURN IF ( SELECTEDVALUE ( PeriodWithDifference[Period] ) = "Difference", _Diff, _PeriodValue )Please see the attached sample pbix.
I have attempted the latest suggestion....The issue is there is toggle slicer which allows the units in the Matrix for the Monthly data to be displayed either in percentages or numeric figures....
The Parameter Field behind the Toggle is:
Parameter = {("<Figures>", NAMEOF('<Data_Table>'[<Measure_Figures>]), 0),("<Percentages>", NAMEOF('<Data_Table>'<Measure_Percentages>), 1)}So I am not using a single measure equivalent to "Total Revenue" (which you have used within your measure named Period With Difference Measure).
So the new single column proposed for the Matrix needs to accommodate interchangeably either [% of Current Month] subtract [% of Previous Month] or [Total Figure of Current Month] subtract [Total Figure of Previous Month] - depending on whichever of the Units the Viewer clicks on the toggle slicer.
Or would it be practical to go for a simpler solution ?
