How to do a running quarterly total on a measure?

[BenBlackswan]

Hello everyone, 

I have a DAX that does distinctcount of a column with some filters, and then I created a matrix with financial year on the rows and Quarter YTD on the column, like this

The DAX code for this measure is

02.1 Clients Exiting OoHC =
VAR _result = 
    CALCULATE(
        distinctCOUNT( fct_placement[person_id] ),
        FILTER(
            fct_placement,
            fct_placement[cat] = "Placement"
            && fct_placement[placement_oohc] = "OOHC"
            && fct_placement[startdate] <> fct_placement[enddate]
        )
        , USERELATIONSHIP(dim_date[Date],fct_placement[episode_enddate])
    )
RETURN _result

I wish to create a matrix visual that added up each quarter for that year,

for example in the FY 2023-24, it should be 47, 94, 136, 179

any help would be greatly appreciated. 

[ahadkarimi]

Hi @BenBlackswan, you can create a Running Total Measure, try this measure below, and if you encounter any issues, let me know.

Running Quarterly Total OoHC =
VAR CurrentQuarter = MAX(dim_date[Quarter])
VAR CurrentYear = MAX(dim_date[FY])
RETURN
CALCULATE(
    [02.1 Clients Exiting OoHC],
    FILTER(
        ALL(dim_date),
        dim_date[FY] = CurrentYear
        && dim_date[Quarter] <= CurrentQuarter
    )
)

Did I answer your question? If so, please mark my post as the solution!✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

[BenBlackswan]

Hi Ahadkarimi,

Thank you for the reply, I tried your measure.

It doesn't seems to give me the correct number, it is giving me number that are less that what it should be. eg the 2023-24 FY, it is 47, 93, 132, 173 instead of 47, 94, 136, 179.

I thought it might be rounding issue, but since it is distinctcount, that shouldn't be the issue. I have added decimal to the numbers in the matrix.

Are you able to provided another clue, thank you very much.

[BenBlackswan]

okay, the issue is apparently has something to do with distinctcount

if the same person_id come up in both Quarter 1 and Quarter 2, it would be counted once in each quarter, but in running total for Q2, it would be counted once for both Q1 and Q2, hence it would not add up. 

[Anonymous]

Hi @BenBlackswan ， ahadkarimi，thank you for your prompt reply!

Please try as following:

Cumulative =
   SUMX(
       SUMMARIZE(
           dim_date,
           dim_date[FY],
           dim_date[Quarter],
           "DistinctCount",[02.1 Clients Exiting OoHC]
       ),
       [DistinctCount]
   )

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.