Consider sales of the last non blank quarter if the previous quarter is blank (not having any value)

[Bilal0336]

I have two tables:

CALENDER_DIM: [ENDOFMONTH, YEAR, QUARTER, QUARTERNUM]

SALES_FACT: [ENDOFMONTH, PRODUCTID, SALE`S]

I want to show a comparison of the current quarter's sales with the previous quarter's sales, and I am using these measures:
Sales - This Quarter = SUM(SALES_FACT[SALES])

Sales - Previous Quarter (Having Sales) = CALCULATE([Sales - This Quarter],PREVIOUSQUARTER(CALENDAR_DIM[ENDOFMONTH]))

Currently, for the Sales - Previous Quarter column if the previous quarter is not having any sales, it is showing Blank:

But what I want to achieve is that: if the previous quarter is blank, consider the last/previous non-blank quarter sales:
Expected output:

So, for 2021, Q2 was null/blank, in that case for 2021 Q3 for the Sales - Previous Quarter column, instead of considering 2021 Q2, consider 2021 Q1.

Similarly, for 2022, Q1 is not having any sales and it is blank, so for 2022 Q2 for the Sales - Previous Quarter column, instead of considering 2022 Q1, consider 2021 Q4 (Which is having sales).

In short, if the previous quarter is blank, consider the latest previous quarter which is having any sales.

Any guidance or help will be appreciated, Thanks.
@amitchandak @tamerj1 @Greg_Deckler @GuyInACube 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Sales Prev Q nonblank: =
IF (
    HASONEVALUE ( 'Calendar'[Quarter] ),
    IF (
        [Sales this Q:] <> BLANK (),
        CALCULATE (
            [Sales this Q:],
            OFFSET (
                -1,
                FILTER (
                    ADDCOLUMNS (
                        ALL (
                            'Calendar'[Year],
                            'Calendar'[Quarter],
                            'Calendar'[End of quarter date CC]
                        ),
                        "@sales", [Sales this Q:] + 0
                    ),
                    [@sales] <> 0
                ),
                ORDERBY ( 'Calendar'[End of quarter date CC], ASC )
            )
        ),
        CALCULATE (
            [Sales this Q:],
            OFFSET (
                -1,
                ADDCOLUMNS (
                    ALL (
                        'Calendar'[Year],
                        'Calendar'[Quarter],
                        'Calendar'[End of quarter date CC]
                    ),
                    "@sales", [Sales this Q:] + 0
                ),
                ORDERBY ( 'Calendar'[End of quarter date CC], ASC )
            )
        )
    )
)

[Bilal0336]

Thanks, It was what I needed.

[Bilal0336]

Thanks, It was what I needed.