Retrieving Values from Single Column

Hi ArchStanton​,

Assuming Month is a proper Date column and you have one row per month, I would identify the latest two non-blank dates first rather than use FIRSTNONBLANK.

One small thing with the previous-value measure already posted: filtering to every date before the latest date and then taking MAX(Active Caseload) works with your sample because the values are increasing. If an older month had a higher caseload, it could return that instead of the immediately previous month.

I would use:

Current Total =
VAR LatestDate =
    CALCULATE(
        MAX('Caseload'[Month]),
        FILTER(
            ALL('Caseload'),
            NOT ISBLANK('Caseload'[Active Caseload])
        )
    )
RETURN
    CALCULATE(
        MAX('Caseload'[Active Caseload]),
        'Caseload'[Month] = LatestDate
    )
Previous Total =
VAR LatestDate =
    CALCULATE(
        MAX('Caseload'[Month]),
        FILTER(
            ALL('Caseload'),
            NOT ISBLANK('Caseload'[Active Caseload])
        )
    )

VAR PreviousDate =
    CALCULATE(
        MAX('Caseload'[Month]),
        FILTER(
            ALL('Caseload'),
            'Caseload'[Month] < LatestDate
                && NOT ISBLANK('Caseload'[Active Caseload])
        )
    )

RETURN
    CALCULATE(
        MAX('Caseload'[Active Caseload]),
        'Caseload'[Month] = PreviousDate
    )

With your example these return 198 and 178.

Then the comparison can simply be:

Caseload Change = [Current Total] - [Previous Total]

and, if you want a triangle indicator:

Caseload Trend =
SWITCH(
    TRUE(),
    [Caseload Change] > 0, UNICHAR(9650),
    [Caseload Change] < 0, UNICHAR(9660),
    UNICHAR(8212)
)

The current Power BI Card visual also supports reference labels and conditional formatting, so you could show the current value as the callout and the previous value/change underneath rather than needing separate visuals.

Hi,

These are the measures

AC = SUM(Data[Active caseload])

Data available till = CALCULATE(max(Data[Month]),LASTNONBLANK('Calendar'[Date],CALCULATE([AC])))

First caseload value = CALCULATE([AC],firstNONBLANK('Calendar'[Date],CALCULATE([AC])))

Last caseload value = CALCULATE([AC],LASTNONBLANK('Calendar'[Date],CALCULATE([AC])))

Penultimate caseload value = CALCULATE([AC],CALCULATETABLE(LASTNONBLANK('Calendar'[Date],CALCULATE([AC])),DATESBETWEEN('Calendar'[Date],min('Calendar'[Date]),[Data available till]-1)))

Hope this helps.

Hi ArchStanton​,

Thank you for reaching out to Microsoft Fabric Community.

Thank you Praful_Potphode​, ShahRukhSameer​, Ashish_Mathur​ and ShivekMaharaj​GilbertQ​ for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the response provided by the user's for the issue worked?  or let us know if you need any further assistance.

Thanks and regards,
Anjan Kumar Chippa

I've retrieved the latest figure using this DAX which works, how can I get the value before it (the prior month)?

Current Total =

        VAR _lastdate =

                    CALCULATE(MAX('Caseload'[Month]),

                    FILTER('Caseload','Caseload'[Active Caseload] <> BLANK() )

                    )

        RETURN

                    CALCULATE(

                            MAX('Caseload'[Active Caseload]),

                            FILTER('Caseload', 'Caseload'[Month] = _lastdate)

                    )

Hi ArchStanton​,

I’d probably avoid FIRSTNONBLANK for this. Since you want the first/latest available value based on the month, it’s better to use the date column to determine which row you want.

For example, for the latest non-blank value:

Latest Caseload = CALCULATE( MAX(Caseload[Active Caseload]), TOPN( 1, FILTER( ALL(Caseload[Month]), NOT ISBLANK(Caseload[Active Caseload]) ), Caseload[Month], DESC ) )

This would return 198 in your example.

Then you can get the previous available value (178) and compare the two:

Caseload Change = [Latest Caseload] - [Previous Caseload]

Use Caseload Change for the conditional formatting — positive = up, negative = down.

The important thing is that Month should be a proper Date column rather than text.

And hopefully the August update fixes some of the Community posting issues too — having to draw the table manually certainly makes things harder! 🙂

Hi ArchStanton​ 

Please try below code

Latest Non-Blank Month = 
CALCULATE (
    MAX ( 'Caseload'[Month] ),
    FILTER ( ALL ( 'Caseload'[Month] ), NOT ISBLANK ( 'Caseload'[Active Caseload] ) )
)

Previous Non-Blank Month = 
CALCULATE (
    MAX ( 'Caseload'[Month] ),
    FILTER (
        ALL ( 'Caseload'[Month] ),
        'Caseload'[Month] < [Latest Non-Blank Month]
            && NOT ISBLANK ( 'Caseload'[Active Caseload] )
    )
)

Current Total = 
CALCULATE ( 
    SUM ( 'Caseload'[Active Caseload] ), 
    'Caseload'[Month] = [Latest Non-Blank Month] 
)

Previous Total = 
CALCULATE ( 
    SUM ( 'Caseload'[Active Caseload] ), 
    'Caseload'[Month] = [Previous Non-Blank Month] 
)

Caseload Trend = 
VAR Delta = [Current Total] - [Previous Total]
RETURN
    IF ( ISBLANK ( [Current Total] ) || ISBLANK ( [Previous Total] ), BLANK (), Delta )

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful