Help with Cumlative Total please

[Anonymous]

Hi 

I am trying to write a dax to create a cummlative total but is isnt working - can anyone help please?

I have the following table  - in the cumlative column I want it to total up as I have manually inserted below.

The total Headcount measure is  as follows 

Total Headcount = DISTINCTCOUNT('MASTER FILE'[EMP ID])

In the Table "Master FIle" there is a ID number which it is doing a disctinct count on and returning this against a date column called "report month"

I have tried a running total Quick measure and it just isnt totalling as I want it - any help please?

Report Month	Total Headcount	Cumlative Total
01 April 2023	1893	1893
01 May 2023	1913	3806
01 June 2023	1911	5717
01 July 2023	1897	7614

I have tried this 

Running total = CALCULATE(sumx(values('Calendar'[Report Month]),[Total Headcount]),Filter(All('Calendar'),'Calendar'[Report Month] <= max('Calendar'[Report Month])))

But the output is this?

Report Month	Total Headcount	Running total
01/04/2023	1893	18317
01/05/2023	1913	20230
01/06/2023	1911	22141
01/07/2023	1897	24038
01/08/2023	1866	25904
01/09/2023	1862	27766
01/10/2023	1831	29597

[Anonymous]

Thank you all for you help - I managed to get it working by using the following:- 

SUMX(FILTER(ALLSELECTED('Calendar'[Report Month]), 'Calendar'[Report Month]<= MAX('Calendar'[Report Month])),CALCULATE(DISTINCTCOUNT('MASTER FILE'[Headcount])))

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

Running total =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Calendar' ),
                'Calendar'[Report Month] <= MAX ( 'Calendar'[Report Month] )
            ),
            'Calendar'[Report Month]
        ),
        "@result", [Total Headcount]
    ),
    [@result]
)