How to carry forward the data to next month until new data is available?

[AKath_12]

Hi, 

I have struggled with the DAX formula and hope an expert here could help. 

I have a data set that inputs on an approximately yearly basis. However, in my display result, I would like the latest data to be carried forward to the next month until a new data value is available. 

For example, the data input from November 2021 and onwards should display 2.38, and the next data available is September 2022 and onwards should display 2.54. 

Note: I have a date table which actively linked to one of my data sets. However, the average result is pulled from another dataset, which has an inactive relationship with the date table. Not sure if this is relevant, hope this provides more information about the solution. Many thanks!

[SachinNandanwar]

I have used a sample data that has Date in the same table as the values.

Sample Data >>

and used the following measure to get the expected output.

MEASURE =
VAR _Curr_Date =
    MAX ( '1'[Date] )
VAR _SummarizeVar =
    CALCULATE (
        MAX ( '1'[Date].[Date] ),
        FILTER ( ALL ( '1' ), [Date] < _Curr_Date && [Value] > 0 )
    )
VAR _LatestValue =
    LOOKUPVALUE ( '1'[Value], '1'[Date], _SummarizeVar )
RETURN
    IF (
        SELECTEDVALUE ( '1'[Value], 0.00 ) = 0,
        _LatestValue,
        SELECTEDVALUE ( '1'[Value] )
    )

You can adjust the above measure to include the relationship across the Date table.

Output >>

[AKath_12]

Thank you very much @SachinNandanwar and @dharmendars007 

I tried to combine both DAX formulas, however, the result was not as expected. So I changed it to the formula below instead, it turns out gave me consecutive results for each month, however, it auto-average the result and the month displayed did not seem quite right.

CALCULATE('Scores by Questions'[Average Score this year],

    USERELATIONSHIP('Date'[Date],'All'[Start Date]),

    FILTER(ALL('Date'),'Date'[Date]<= MAX('Date'[Date])))

2.54 should have been displayed from November onwards, however, the result showed 2.46, and displayed wrongly from Sept onwards instead.

The same goes for result 3.44 should be displayed from Dec 2023 onwards, however, 2.71 displayed in June 2023 instead.

The indirect relationship that I have used is Many to one and single cross-filter direction. 

Looking forward to more of your advice! Thank you!

[SachinNandanwar]

Please share the data.

[dharmendars007]

Hello @AKath_12 , 

Since you mentioned that the "average result" is pulled from a dataset with an inactive relationship to the date table, make sure that you activate this relationship within your DAX query when needed. You can use USERRELATIONSHOT() to activate the inactive relationship.

Carry Forward Result =
VAR LastValue =
CALCULATE(
MAX('YourDataTable'[Result]),USERELATIONSHIP('YourDateTable'[Date], 'YourDataTable'[Date]),
FILTER(ALL('YourDataTable'),'YourDataTable'[Date] <= MAX('YourDateTable'[Date])))
RETURN
LastValue

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN