DAX Power BI Desktop

Anonymous

In this scenario, you need to get the last non blank Panned-Earned's Cut_Off_Date, have it compare with current date to determine if it should return half of the last non blank Panned-Earned. Please refer to my sample below:

LastNonBlankDate =
CALCULATE (
    MAX ( 'Table'[Cut_Off_Date] ),
    FILTER (
        'Table',
        'Table'[Cut_Off_Date] < EARLIER ( 'Table'[Cut_Off_Date] )
            && 'Table'[ID] = EARLIER ( 'Table'[ID] )
            && 'Table'[Panned-Earned] <> BLANK ()
    )
)

DateDiff = 1*('Table'[Cut_Off_Date]-'Table'[LastNonBlankDate])

Column =
IF (
    'Table'[Panned-Earned] = 0,
    IF (
        'Table'[DateDiff] <= 14,
        CALCULATE (
            LASTNONBLANK ( 'Table'[Panned-Earned], 1 ),
            FILTER (
                'Table',
                'Table'[Cut_Off_Date] < EARLIER ( 'Table'[Cut_Off_Date] )
                    && 'Table'[ID] = EARLIER ( 'Table'[ID] )
            )
        )
            / 2,
        BLANK ()
    ),
    'Table'[Panned-Earned]
)

If you just want to build one column, it can be like:

Column =
IF (
    'Table'[Panned-Earned] = 0,
    IF (
        1
            * (
                'Table'[Cut_Off_Date]
                    - CALCULATE (
                        MAX ( 'Table'[Cut_Off_Date] ),
                        FILTER (
                            'Table',
                            'Table'[Cut_Off_Date] < EARLIER ( 'Table'[Cut_Off_Date] )
                                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
                                && 'Table'[Panned-Earned] <> BLANK ()
                        )
                    )
            )
            <= 14,
        CALCULATE (
            LASTNONBLANK ( 'Table'[Panned-Earned], 1 ),
            FILTER (
                'Table',
                'Table'[Cut_Off_Date] < EARLIER ( 'Table'[Cut_Off_Date] )
                    && 'Table'[ID] = EARLIER ( 'Table'[ID] )
            )
        )
            / 2,
        BLANK ()
    ),
    'Table'[Panned-Earned]
)

Regards,

Hi Simon,

Thank you very much for your reply. I need to verify the solution in my desktop and give you a Feedback, thanks.

Regards

Henrik