Forum Discussion

harshadrokade's avatar
harshadrokade
Post Partisan
30 days ago
Solved

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, N...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    30 days ago

    harshadrokade 

    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)
        )
    RETURN
        COALESCE(CurrVal, PrevVal)
     
     
    pls see the attachment below
Kedar_Pande's avatar
Kedar_Pande
Super User
30 days ago

harshadrokade 

 

Create 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-pande