Forum Discussion
Assistance Required - Power BI Visual Matrix
Hi Tejas
Ok, great to hear that there is some progress. I took another look to see if I could get rid of the empty columns . I could only find a solution in Power Query and this was the result :
In general the steps taken are as follows : Identifies the Maxmonth and two months before ( Query : MaxMonthQuery ) , which joins with the Grouped average per assignment group ( Query : GroupedAverageQuery ) . This then finally joins with the MaindataQuery ( filtered to show only 3 months prior to max date of the data ) to merge the average SLA% per group.
For the updated file see the same folder I placed the other Power BI files in.
Hope this helps
Antonio
Tried removefilters no luck.
Hi Lakhani04
Below is another possible solution without Removefilters :
SLA % Last 3M =
VAR MaxDate =
CALCULATE(
MAX(Data[Date]),
ALL(Data)
)
VAR Last3Months =
DATESINPERIOD(
'Data'[Date],
MaxDate,
-3,
MONTH
)
RETURN
CALCULATE(
AVERAGE('Data'[SLA %]),
Last3Months,
ALLEXCEPT('Data', 'Data'[Assignment Group])
)
with the below result :
Below is my Power BI file if required , feel free to test with a sample of your data.
Power BI Community 25 July 2025
If this amendment does not work , please povide further details ( error message, extra screenshots or a sample file ) .
Antonio
Hello Antonio,
Thanks a lot for sharing this file and helping me.
please have look at the below SS when i add 3 month SAL it apperts for all the month I need it as new column in end.
need to show output like below:Groups Jan Feb Mar 3 month SAL Group A 0.87 0.92 0.89 1 Group B 0.91 0.88 0.93 23 Group C 0.95 0.9 0.85 2 Group D 0.86 0.94 0.87 3 Thanks for the extra information , below is an amended measure :
SLA % Last 3M (Only Latest Month) = VAR MaxDate = CALCULATE(MAX('Data'[Date]), ALL('Data')) VAR MaxMonth = EOMONTH(MaxDate, 0) VAR CurrentMonth = EOMONTH(MAX('Data'[Date]), 0) VAR Last3Months = DATESINPERIOD( 'Data'[Date], MaxDate, -3, MONTH ) RETURN IF ( CurrentMonth = MaxMonth, CALCULATE( AVERAGE('Data'[SLA %]), Last3Months, ALLEXCEPT('Data', 'Data'[Assignment Group]) ) )The formula finds the latest month in the data and if the context is the latest month it calculates the average SLA % over the last 3 months ( else it remains blank )
The header of this new measure is a bit of challenge. I selected Specific column in formatting options and and changed the text to white so that it does not appear for each month. I then added text below below the table to indicate what the last column shows.
Below is the amended file.
Hope this is closer to what you require
Antonio
Hi Antonio,
Thank you so much for the updated file and the revised DAX. I truly appreciate the workaround you’ve implemented—it's a clever solution!
That said, I’m looking for a more permanent fix where the Matrix table doesn’t generate that empty column at all. If there’s a way to eliminate it entirely at the source, that would be ideal.
Thanks again for your support and great work on this!
Rgards,
Tejas Lakhani