rolling 3 day total issue (when original date has blank value)

[Howie_fishman]

Hello. I am using the following DAX code to succesfully create a measure giving 3 day sum of "mortality count" on the date and 2 following dates when mort count is not blank.

The problem is, it will not return a value if the starting date is blank for "mortality count". 

 

 how can i tweak this so that it will always return the next 3 non blank "mortality count" values based on the date.

How can I ensure that 

3 day rolling post morts =

VAR Date1 =

    max ( 'Calendar'[Date] )

VAR Date2 =

    MINX (

        FILTER (

            ALL ( 'Calendar'[Date] ),

            'Calendar'[Date] > Date1

                && NOT ( ISBLANK ( [Mortality Count] ) )

        ),

        'Calendar'[Date]

    )

VAR Date3 =

    MINX (

        FILTER (

            ALL ( 'Calendar'[Date] ),

            'Calendar'[Date] > Date2

                && NOT ( ISBLANK ( [Mortality Count] ) )

        ),

        'Calendar'[Date]

    )

VAR Sum1 =

    CALCULATE ( [Mortality Count], 'Calendar'[Date] = Date1 )

VAR Sum2 =

    CALCULATE ( [Mortality Count], 'Calendar'[Date] = Date2 )

VAR Sum3 =

    CALCULATE ( [Mortality Count], 'Calendar'[Date] = Date3 )

RETURN

    IF (

        ISBLANK ( [Mortality Count] ),

        BLANK (),

        Sum1 + Sum2 + Sum3

    )

[Anonymous · Accepted solution]

Hi @Howie_fishman ,

Thanks for Greg_Deckler's reply!
And @Howie_fishman , I have no idea what your data looks like, and the screenshot you provided is blurry, so I created sample data myself for testing:

Then use this DAX to create a measure:

3 day rolling post morts = 
VAR Date1 =
    MAX ( 'Calendar'[Date] )
VAR NextNonBlankDates =
    TOPN (
        3,
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] >= Date1
                && NOT ( ISBLANK ( [Mortality Count] ) )
        ),
        'Calendar'[Date], ASC
    )
VAR SumMortality =
    SUMX (
        NextNonBlankDates,
        [Mortality Count]
    )
RETURN
    SumMortality

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Howie_fishman First, try modifying your Value filter behavior to be Independent. Work with value filter behavior in Power BI - Power BI | Microsoft Learn