DAX to return latest data based on the slicer selection

[apanta]

Hi,

Please help me with the Dax that I have been stuck with for the past few days.

I am working on one of the Power BI reports where the requirement of the user is when a certain month and year are selected on the slicer, then it gives the selected month/year data. But if there’s no data for that month, then it should pick the latest data from the past months but within a 12-month period. For example: on the below table, when the slicer is selected as March 2023 then it should get the value 10 but if February 2023 is selected, since there’s no value for February, it should pick up from January 2023.

Similarly, if December 2022 is selected, since there’s no value for October and November 2022, it should pick up from September 2022 (as it is the latest available data).

Date Created	Value
1/3/2023	10
1/2/2023	0
1/1/2023	9
1/12/2022	0
1/11/2022	0
1/10/2022	0
1/9/2022	11
1/8/2022	12
1/7/2022	13
1/6/2022	0
1/5/2022	0
1/4/2022	14
1/3/2022	8
1/2/2022	7

2nd case would be, if March 2023 is selected then it should look up for the latest data till March 2022, if nothing is available then return the value as zero. It shouldn’t pick up the February 2022 value.

Date Created	Value
1/3/2023	0
1/2/2023	0
1/1/2023	0
1/12/2022	0
1/11/2022	0
1/10/2022	0
1/9/2022	0
1/8/2022	0
1/7/2022	0
1/6/2022	0
1/5/2022	0
1/4/2022	0
1/3/2022	0
1/2/2022	7

Any help/direction would be greatly appreciated.

Thank you!

[johnt75 · Accepted solution]

Try

My Measure =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR PrevDate =
    DATE ( YEAR ( ReferenceDate ) - 1, MONTH ( ReferenceDate ), DAY ( ReferenceDate ) )
VAR Result =
    CALCULATE (
        LASTNONBLANKVALUE ( 'Table'[Date Created], SUM ( 'Table'[Value] ) ),
        'Table'[Date Created] >= PrevDate
            && 'Table'[Date Created] <= ReferenceDate
    )
RETURN
    Result

[apanta]

Unfortunately, it didn't work. It only shows the value for that particular month only.

[johnt75 · Accepted solution]

Add REMOVEFILTERS(Date_dim) into the CALCULATE statement.

[apanta]

Hi John,

It gave me the same result as before.

[johnt75]

Could any other filters be affecting it ?

Are you sure that data is missing? Try and find an entry which has no data for a given month, only data prior to that, and see if it appears in the month which it is missing data for.

[apanta]

Hi John,

I found the problem with my measure itself and have fixed it and now it's working fine with the Dax you provided.

Thank you for your help 🙂

[johnt75 · Accepted solution]

Try

My Measure =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR PrevDate =
    DATE ( YEAR ( ReferenceDate ) - 1, MONTH ( ReferenceDate ), DAY ( ReferenceDate ) )
VAR Result =
    CALCULATE (
        LASTNONBLANKVALUE ( 'Table'[Date Created], SUM ( 'Table'[Value] ) ),
        'Table'[Date Created] >= PrevDate
            && 'Table'[Date Created] <= ReferenceDate
    )
RETURN
    Result

[apanta]

Hi John,
Thank you for your reply! look like I am near to my goal. In place of value, I am using the calculated measure, will the "last non-blank value" dax work for the measure as well? I tried but it didn't give me the right answer.

var Result = CALCULATE(LASTNONBLANKVALUE('Table'[DateCreated], [Measure]), 'Table'[DateCreated] >= PrevDate && 'Table'[DateCreated]<= ReferenceDate)

Kind Regards,

[johnt75]

That should work. Try creating a table visual with the date created column and your measure in it. If that shows you the expected results then LASTNONBLANKVALUE should work.