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.

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

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.

  • ArchStanton's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    1 month ago

    Thanks for your help with this, 3 out of the 4 measures work, its the penultimate caseload measure  thats failing. Please note that the Month Column within the Caseload table is a date field that is linked to my Date Calendar table.

    Penultimate Caseload value = 
CALCULATE (
    [Active Caseload Measure],
    CALCULATETABLE (
        LASTNONBLANK (Caseload[Month], CALCULATE ( [Active Caseload Measure] ) ),
        DATESBETWEEN (
            Caseload[Month],
            MIN ( Caseload[Month] ),
            [Data available till] - 1
        )
    )
)

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      1 month ago

      Hi,

      Share the download link of your PBI file with your formulas already written there.