How to repeat the latest measure result that doesn't show blank

[Tings2]

Hi,

I have a measure that returns a result that I want to show for each month. However, the data behind the measure is not available for all months and will therefore return a blank when no data is available in the underlying fact table.

I have been asked to repeat the latest available result for each month until new data is available. I have pasted an example of what I'm trying to achieve below:

Current Result

 

Desired Result

Month Year	Result
Nov-22	15
Dec-22	15
Jan-23	10
Feb-23	10
Mar-23	10
Apr-23	10
May-23	10

Model Snapshot

The measure calculates a result using a field in the All Metrics table which is then aggregated up to a Control from the Controls table.

The Month Year field is from the Date table which is linked to day_date in the All Metrics table via fulldatealternatekey.

Measure Snapshot

The current measure in use will return a 0 when there is no data available for a given month which is the correct behaviour in most contexts. 

However, I need a separate measure to repeat the latest result by month year.

Current Measure

Proposed Measure

I've been attempting a number of variants on the below measure:

The trouble is I can easily remove the filter context being applied to the day_date  in the visual and return a non blank result for all records in the all_metrics table but I can't reapply the filter context to make sure only the latest result is returned.

If someone could please help me work out to show only the latest non blank result that would be so helpful.

Thank you!

[Jihwan_Kim · Accepted solution]

HI,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

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

Result measure: = 
VAR _currentrow =
    MAX ( 'Calendar'[Month Year sort] )
VAR _monthyeartable =
    ADDCOLUMNS (
        FILTER (
            ALLSELECTED ( 'Calendar'[Month Year], 'Calendar'[Month Year sort] ),
            'Calendar'[Month Year sort] <= _currentrow
        ),
        "@result", CALCULATE ( SUMX ( Data, Data[Price] * Data[Qty] ) )
    )
VAR _nonzeromaxmonthyear =
    MAXX ( FILTER ( _monthyeartable, [@result] <> 0 ), 'Calendar'[Month Year sort] )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[Month Year] ),
        CALCULATE (
            SUMX ( Data, Data[Price] * Data[Qty] ),
            FILTER (
                ALLSELECTED ( 'Calendar' ),
                'Calendar'[Month Year sort] = _nonzeromaxmonthyear
            )
        )
    )