Forum Discussion
Last 3 quarter
I have a data set in which contain Sales amount, Name of manager, Report pull date, Quarter
I need to build a Matrix table that will show last 3 quarter sales for each manager based on the Quarter selected from the slicer.
Like if we select Qtr 2 2025. It should show Qtr 2 2025, Qtr 1 2025 & Qtr 4 2024.
| Name | QTR 2 2025 | QTR 1 2025 | QTR 4 2025 |
| Alex | $ 234.00 | $ 256.00 | $ 432.00 |
| Tom | $ 321.00 | $ 531.00 | $ 422.00 |
| Glen | $ 303.00 | $ 585.00 | $ 517.00 |
| Max | $ 551.00 | $ 372.00 | $ 299.00 |
| Rex | $ 431.00 | $ 495.00 | $ 235.00 |
How can we achive this
Hi unnijoy,
Thank you for your follow up.
Please find attached a screenshot and a sample PBIX file that may help resolve the issue:
We hope the information provided is useful. If you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
18 Replies
Hi unnijoy,
Thank you for your follow up.
Please find attached a screenshot and a sample PBIX file that may help resolve the issue:
We hope the information provided is useful. If you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
v-pnaroju-msft Thank you very much. Its working. 😊
v-pnaroju-msft in this table insted of the numbers if i need to showe the grand total % how should i do that.
You'll need a proper date table, marked as a date table. Transform your data, e.g. using Power Query, so that instead of a string like 'Q1 2025' you have the date representing the first day of that quarter, then link the date column of the date table to the column representing the start of the quarter in your fact table.
Once that is in place, you can adapt the technique described in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ , instead of using a month offset in the call to DATESINPERIOD use quarter instead.
Thankyou, johnt75, for your response.
Hi unnijoy,We appreciate your inquiry through the Microsoft Fabric Community Forum.
Based on my understanding of the scenario, we have created a Date table with the columns QuarterStartDate and QuarterLabel, and marked it as the Date table. We added a disconnected Quarter Slicer table to drive the selection. We also wrote a measure, Sales Last 3 Quarters, using DATESINPERIOD to calculate sales for the selected quarter and the two preceding quarters, and another measure serving as a Flag that returns 1 only for those three quarters. Finally, we built a Matrix visual.
Please find the attached screenshot and a sample PBIX file, which may help resolve the issue:
We hope the information provided assists in resolving the matter. Should you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
v-pnaroju-msft , thanks a lot for your reply. Here i found one issue . If i have other visuals then based on the select filter the values are not chaning.. For example i need to show the total sales of each manager for QTR 1 in a Pie chart then if i select the QTR slicer it is not changing the values. so how can i make the Quarter slicer make available for all visuals.
Build a Date table with Year + Quarter + YearQuarter (e.g. "Q2 2025").
Add a QuarterIndex column using RANKX to order quarters.Create measure:
ShowLast3Quarters =
VAR SelIndex = MAXX(FILTER(ALL('Date'), 'Date'[YearQuarter] = SELECTEDVALUE('Date'[YearQuarter])), 'Date'[QuarterIndex])
VAR CurrIndex = MAX('Date'[QuarterIndex])
RETURN IF(CurrIndex <= SelIndex && CurrIndex >= SelIndex - 2, 1, 0)
n Matrix → Rows = Manager, Columns = YearQuarter, Values = SUM(Sales).
Apply filter ShowLast3Quarters = 1.
Now slicer pick (e.g. Q2 2025) → shows Q2 2025, Q1 2025, Q4 2024.Shahid12523 , thank you for your reply. I tried this. But when i select a QTR it is showing only that QTR Data.
What should be the Rankx dax i have to write. Currently i write it asRANKX(Calander,Calander[YearQtr])Try this
Qtr_index =
RANKX (
ALL ( 'Calander'[YearQtr] ),
CALCULATE ( MIN ( 'Calander'[Date] ) ),
,
ASC,
DENSE
)
Hi unnijoy,
Thank you for your follow up.
Please find attached a screenshot and a revised sample PBIX file that may help resolve the issue:
We hope the information provided is useful. If you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
v-pnaroju-msft , thanks for your help. I have made some change in the table. and when i try to apply it this their i am getting all the values as 100%. Please check the below link to the post where i explain the table details.
https://community.fabric.microsoft.com/t5/Desktop/Total/m-p/4817762#M1432643
Hi unnijoy,
Thank you for your update. We appreciate you raising a new query in the Microsoft Fabric Community forum. Dedicated support engineers will respond to your query with appropriate solutions. Kindly feel free to contact the Microsoft Fabric Community for any future queries.
Thank you.
Hi,
I have solved a similar question in the attached PBI file.
