Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
2 months ago
Solved

Retrieving Values from Single Column

Hi, I have the following simple table in my Datamodel: Month                                       Active caseload 01 March 2026                                  100 April 2026                   ...
Need Help
  • aswathimohan's avatar
    aswathimohan
    2 months ago

    I have realised my mistake and deleted that post. I had also tried a solution as 

    Current Total = 
VAR _T = 
    TOPN(
        1,
        FILTER(CaseLoad,CaseLoad[Active Caseload]<>BLANK()),
        MONTH(CaseLoad[Month Year]),DESC)


RETURN
MAXX(_T,CaseLoad[Active Caseload])
    Previous Total = 
VAR _T = 
    TOPN(
        2,
        FILTER(CaseLoad,CaseLoad[Active Caseload]<>BLANK()),
        MONTH(CaseLoad[Month Year]),DESC)


VAR _previous_date = MINX(_T,CaseLoad[Month Year])

RETURN
CALCULATE(SUM(CaseLoad[Active Caseload]),CaseLoad[Month Year]=_previous_date)

    Is this a right approach.

ShahRukhSameer's avatar
ShahRukhSameer
Icon for Solution Sage rankSolution Sage
2 months ago

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! 🙂