Forum Discussion
Showing Prev Qtr data in matrix visual
Hi All,
I have a table as Fact Sales. I have a Dim Date table (MIS date column) which is connected with MIS date column from Fact Sales.
I have created a matrix visual as below-
Rows- ID, Name
Columns- Sales Qtr (Quarterly dates)
Value-Sum of Sales Amount
Slicer on page- MIS date from Dim date table (Quarterly dates)
In Matrix visual, I want to show previous qtr Sales amount if a qtr doesnt have Sales amount. E.g. For ID-ID1, Name-A1, for Mar25 Sales date, for Sep23 Sales Qtr doesnt have Sales amount and so I want to show Jun23 Qtr Sales amount for Sep23 i.e. 1027. When the Sales amount is not there for a Sales Qtr of A ID and Name, that Sales qtr row doesnt exist in data. Refer below data for for Sep23 which is not there in base data as Sales amount is not there. And still I want to show the prev Sales qtr's Sales amount in matrix, for tht missing Qtr date.
Request you to Provide a soluton for the same.
Data-
| Name | ID | Sales Qtr | MIS Date | Sales Amount |
| A1 | ID1 | 6/30/2022 | 3/31/2025 | 706 |
| A1 | ID1 | 9/30/2022 | 3/31/2025 | 869 |
| A1 | ID1 | 12/31/2022 | 3/31/2025 | 486 |
| A1 | ID1 | 3/31/2023 | 3/31/2025 | 1664 |
| A1 | ID1 | 6/30/2023 | 3/31/2025 | 1027 |
| A1 | ID1 | 12/31/2023 | 3/31/2025 | 1685 |
| A1 | ID1 | 3/31/2024 | 3/31/2025 | 1582 |
| A1 | ID1 | 6/30/2024 | 3/31/2025 | 1248 |
| A1 | ID1 | 9/30/2024 | 3/31/2025 | 981 |
| A1 | ID1 | 12/31/2024 | 3/31/2025 | 301 |
| A1 | ID1 | 3/31/2025 | 3/31/2025 | 751 |
| A1 | ID1 | 6/30/2022 | 6/30/2025 | 947 |
| A1 | ID1 | 9/30/2022 | 6/30/2025 | 973 |
| A1 | ID1 | 6/30/2023 | 6/30/2025 | 1016 |
| A1 | ID1 | 9/30/2023 | 6/30/2025 | 769 |
| A1 | ID1 | 12/31/2023 | 6/30/2025 | 842 |
| A1 | ID1 | 3/31/2024 | 6/30/2025 | 1223 |
| A1 | ID1 | 6/30/2024 | 6/30/2025 | 1076 |
| A1 | ID1 | 9/30/2024 | 6/30/2025 | 1573 |
| A1 | ID1 | 12/31/2024 | 6/30/2025 | 1615 |
| A1 | ID1 | 3/31/2025 | 6/30/2025 | 306 |
| A1 | ID1 | 6/30/2025 | 6/30/2025 | 984 |
| A1 | ID1 | 6/30/2022 | 9/30/2025 | 504 |
| A1 | ID1 | 9/30/2022 | 9/30/2025 | 874 |
| A1 | ID1 | 12/31/2022 | 9/30/2025 | 1412 |
| A1 | ID1 | 3/31/2023 | 9/30/2025 | 1656 |
| A1 | ID1 | 6/30/2023 | 9/30/2025 | 1093 |
| A1 | ID1 | 9/30/2023 | 9/30/2025 | 631 |
| A1 | ID1 | 12/31/2023 | 9/30/2025 | 749 |
| A1 | ID1 | 3/31/2024 | 9/30/2025 | 664 |
| A1 | ID1 | 6/30/2024 | 9/30/2025 | 151 |
| A1 | ID1 | 9/30/2024 | 9/30/2025 | 1784 |
| A1 | ID1 | 12/31/2024 | 9/30/2025 | 701 |
| A1 | ID1 | 3/31/2025 | 9/30/2025 | 1636 |
| A1 | ID1 | 6/30/2025 | 9/30/2025 | 256 |
| A1 | ID1 | 9/30/2025 | 9/30/2025 | 1184 |
| A2 | ID2 | 9/30/2022 | 3/31/2025 | 1445 |
| A2 | ID2 | 12/31/2022 | 3/31/2025 | 495 |
| A2 | ID2 | 3/31/2023 | 3/31/2025 | 1913 |
| A2 | ID2 | 6/30/2023 | 3/31/2025 | 1404 |
| A2 | ID2 | 9/30/2023 | 3/31/2025 | 1154 |
| A2 | ID2 | 12/31/2023 | 3/31/2025 | 401 |
| A2 | ID2 | 3/31/2024 | 3/31/2025 | 487 |
| A2 | ID2 | 6/30/2024 | 3/31/2025 | 569 |
| A2 | ID2 | 9/30/2024 | 3/31/2025 | 1776 |
| A2 | ID2 | 12/31/2024 | 3/31/2025 | 259 |
| A2 | ID2 | 3/31/2025 | 3/31/2025 | 211 |
| A2 | ID2 | 12/31/2022 | 6/30/2025 | 1569 |
| A2 | ID2 | 3/31/2023 | 6/30/2025 | 774 |
| A2 | ID2 | 6/30/2023 | 6/30/2025 | 925 |
| A2 | ID2 | 9/30/2023 | 6/30/2025 | 1046 |
| A2 | ID2 | 12/31/2023 | 6/30/2025 | 443 |
| A2 | ID2 | 3/31/2024 | 6/30/2025 | 1135 |
| A2 | ID2 | 6/30/2024 | 6/30/2025 | 160 |
| A2 | ID2 | 9/30/2024 | 6/30/2025 | 724 |
| A2 | ID2 | 12/31/2024 | 6/30/2025 | 1647 |
| A2 | ID2 | 3/31/2025 | 6/30/2025 | 1239 |
| A2 | ID2 | 6/30/2025 | 6/30/2025 | 1552 |
| A2 | ID2 | 6/30/2022 | 9/30/2025 | 988 |
| A2 | ID2 | 9/30/2022 | 9/30/2025 | 1831 |
| A2 | ID2 | 12/31/2022 | 9/30/2025 | 476 |
| A2 | ID2 | 3/31/2023 | 9/30/2025 | 1885 |
| A2 | ID2 | 6/30/2023 | 9/30/2025 | 666 |
| A2 | ID2 | 9/30/2023 | 9/30/2025 | 1827 |
| A2 | ID2 | 12/31/2023 | 9/30/2025 | 1817 |
| A2 | ID2 | 3/31/2024 | 9/30/2025 | 1668 |
| A2 | ID2 | 6/30/2024 | 9/30/2025 | 1558 |
| A2 | ID2 | 9/30/2024 | 9/30/2025 | 625 |
| A2 | ID2 | 12/31/2024 | 9/30/2025 | 1864 |
| A2 | ID2 | 3/31/2025 | 9/30/2025 | 162 |
| A2 | ID2 | 6/30/2025 | 9/30/2025 | 409 |
| A2 | ID2 | 9/30/2025 | 9/30/2025 | 1193 |
| A3 | ID2 | 6/30/2022 | 3/31/2025 | 928 |
| A3 | ID2 | 9/30/2022 | 3/31/2025 | 273 |
| A3 | ID2 | 12/31/2022 | 3/31/2025 | 1249 |
| A3 | ID2 | 12/31/2023 | 3/31/2025 | 980 |
| A3 | ID2 | 3/31/2024 | 3/31/2025 | 1595 |
| A3 | ID2 | 6/30/2024 | 3/31/2025 | 1095 |
| A3 | ID2 | 12/31/2024 | 3/31/2025 | 1353 |
| A3 | ID2 | 3/31/2025 | 3/31/2025 | 373 |
| A3 | ID2 | 6/30/2022 | 6/30/2025 | 1063 |
| A3 | ID2 | 3/31/2023 | 6/30/2025 | 1365 |
| A3 | ID2 | 6/30/2023 | 6/30/2025 | 1160 |
| A3 | ID2 | 9/30/2023 | 6/30/2025 | 635 |
| A3 | ID2 | 12/31/2023 | 6/30/2025 | 1468 |
| A3 | ID2 | 3/31/2024 | 6/30/2025 | 1654 |
| A3 | ID2 | 6/30/2024 | 6/30/2025 | 1247 |
| A3 | ID2 | 9/30/2024 | 6/30/2025 | 800 |
| A3 | ID2 | 12/31/2024 | 6/30/2025 | 1031 |
| A3 | ID2 | 3/31/2025 | 6/30/2025 | 930 |
| A3 | ID2 | 6/30/2025 | 6/30/2025 | 1569 |
| A3 | ID2 | 6/30/2022 | 9/30/2025 | 1954 |
| A3 | ID2 | 9/30/2022 | 9/30/2025 | 463 |
| A3 | ID2 | 12/31/2022 | 9/30/2025 | 770 |
| A3 | ID2 | 3/31/2023 | 9/30/2025 | 1324 |
| A3 | ID2 | 6/30/2023 | 9/30/2025 | 434 |
| A3 | ID2 | 9/30/2023 | 9/30/2025 | 1847 |
| A3 | ID2 | 12/31/2023 | 9/30/2025 | 663 |
| A3 | ID2 | 3/31/2024 | 9/30/2025 | 964 |
| A3 | ID2 | 6/30/2024 | 9/30/2025 | 692 |
| A3 | ID2 | 9/30/2024 | 9/30/2025 | 181 |
| A3 | ID2 | 12/31/2024 | 9/30/2025 | 1812 |
| A3 | ID2 | 3/31/2025 | 9/30/2025 | 818 |
| A3 | ID2 | 6/30/2025 | 9/30/2025 | 1771 |
| A3 | ID2 | 9/30/2025 | 9/30/2025 | 1413 |
in your sample data, I found the value for A1 in 2023 3rd Q
then i delete thses two rows
you can create a dim table and create a measure
Measure =VAR CurrQtr = SELECTEDVALUE('Table 2'[Sales Qtr])VAR CurrVal =CALCULATE(SUM('Table'[Sales Amount]))VAR PrevVal =CALCULATE(SUM('Table'[Sales Amount]),REMOVEFILTERS('Table 2'),'Table 2'[Sales Qtr] = EOMONTH(CurrQtr, -3))RETURNCOALESCE(CurrVal, PrevVal)pls see the attachment below
7 Replies
To place the DimDate quarter on the matrix Columns so that all quarters appear, including those with no FactSales rows. Then use a DAX measure that returns the sales for the selected quarter, or when FactSales has no row for that quarter the sales of the previous quarter.
Hi,
Create an inactive relationship between the Sales Qtr column and the Date column of the Calendar table. Write these measures
Sales = sum(Data[Sales amount])
Sales in PQ = calculate([Sales],previousquarter(Calendar[Date]),userelationship(Data[Sales qtr],Calendar[Date]))
Hope this helps.
in your sample data, I found the value for A1 in 2023 3rd Q
then i delete thses two rows
you can create a dim table and create a measure
Measure =VAR CurrQtr = SELECTEDVALUE('Table 2'[Sales Qtr])VAR CurrVal =CALCULATE(SUM('Table'[Sales Amount]))VAR PrevVal =CALCULATE(SUM('Table'[Sales Amount]),REMOVEFILTERS('Table 2'),'Table 2'[Sales Qtr] = EOMONTH(CurrQtr, -3))RETURNCOALESCE(CurrVal, PrevVal)pls see the attachment belowCreate a measure and use it in the matrix instead of the raw Sales Amount column:
Sales Amount (Prev Qtr Fallback) =
VAR CurrentQtr = MAX('Fact Sales'[Sales Qtr])
VAR ActualAmount = SUM('Fact Sales'[Sales Amount])
VAR PrevAvailableQtr =
CALCULATE(
MAX('Fact Sales'[Sales Qtr]),
FILTER(
ALL('Fact Sales'[Sales Qtr]),
'Fact Sales'[Sales Qtr] < CurrentQtr
)
)
VAR PrevQtrAmount =
CALCULATE(
SUM('Fact Sales'[Sales Amount]),
ALL('Fact Sales'[Sales Qtr]),
'Fact Sales'[Sales Qtr] = PrevAvailableQtr
)
RETURN
IF(ISBLANK(ActualAmount), PrevQtrAmount, ActualAmount)If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pandeHi harshadrokade,
Have you had a chance to review the solution shared by Kagiyama_yutaka, Ashish_Mathur, ryan_mayu, Kedar_Pande ? If the issue persists, feel free to reply so we can help further.
Thank you.
Hi harshadrokade,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
Hi harshadrokade,
Since the missing quarter doesn't exist as a row in your fact table, a regular SUM(Sales Amount) won't be able to return anything for that quarter. What you need is a carry-forward measure that returns the most recent available Sales Amount for the same ID/Name whenever the current quarter is missing.
The first thing I'd check is that your Matrix is using the Quarter from your Date table and that "Show items with no data" is enabled. Otherwise, Power BI won't even display the missing quarter.
Then, instead of using a simple sum measure, try creating a measure that returns the last non-blank value:
Sales Amount Carry Forward =
VAR CurrentQtr = MAX('Dim Date'[MIS Date])
VAR LastQtrWithData = CALCULATE( MAX('Fact Sales'[Sales Qtr]), FILTER( ALL('Fact Sales'[Sales Qtr]), 'Fact Sales'[Sales Qtr] <= CurrentQtr && NOT ISBLANK( CALCULATE(SUM('Fact Sales'[Sales Amount])) ) ) )
RETURN CALCULATE( SUM('Fact Sales'[Sales Amount]), 'Fact Sales'[Sales Qtr] = LastQtrWithData )
For example, for A1 / ID1 with MIS Date = 31-Mar-2025:
Jun-23 = 1027
Sep-23 = Missing
The measure would return 1027 for Sep-23 by carrying forward the last available quarter's value.
This is usually the approach taken when the requirement is "show the previous quarter's value if the current quarter has no data." The important part is having a complete Date table driving the Matrix columns so that the missing quarters are still displayed even when no fact row exists for that period.
