Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
1 month 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
    1 month 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.

Praful_Potphode's avatar
Praful_Potphode
Icon for Super User rankSuper User
1 month ago

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