Rolling sum

[Anonymous]

Hi,

I am looking at creating a total sum per month as below in the Running Total.

Year	Region	Cat	Category	MONTH	Savings	Running Total
2018	A	XX	Services	January	50	50
2018	A	XX	Services	February	65	115
2018	A	XX	Services	March	45	160
2018	B	YY	Retail	January	21	21
2018	B	YY	Retail	February	45	66
2018	B	YY	Retail	March	23	89
2018	C	ZZ	Equipment	January	12	12
2018	C	ZZ	Equipment	February	55	67
2018	C	ZZ	Equipment	March	66	133

I can see how to do this in DAX but I am little confused as I am working with Text and not dates (I do also have a column called Month Number (1-12) per month which i might be able to use?

Is possible via DAX?

Thanks

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this as a MEASURE

Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)

or this as a CALCULATED COLUMN

Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ),
        Targets[Date] <= EARLIER ( Targets[Date] )
    )
)

[jthomson]

I'd try to create a dummy date field from the information you have - calculate a column through DATE ([Year], [whateveryourmonthnumbercolumniscalled],1) which should create a field with the first day of the month in question

[Anonymous]

Great i have made that but when i put the Dax in to the new mesure it does not roll up per month;

My dax i have used is below;

Saving Roll =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALL ( Targets[Date] ),
        Targets[Date] <= MAX ( Targets[Date] )
    )
)

Wonder what could be going wrong?

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this as a MEASURE

Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)

or this as a CALCULATED COLUMN

Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ),
        Targets[Date] <= EARLIER ( Targets[Date] )
    )
)

[Anonymous]

Arr thought it was 100% but this woks if the slicer is Catrgory but not if it is any of the others slicers 😞

[Zubair_Muhammad]

@Anonymous

When I use your data and use Region as slicer...It seemingly works well.

What slicer did you use?

[Anonymous]

Arr yes but in my whole dataset it is a little different, I have an update example of what could happen.

As you can see from below Region Cat and Category can be different the was luck that Region Cat and Category lined up 😞

That is why the sum always looked correct.

Year	Region	Cat	Category	MONTH	Savings	Running Total
2018	A	XX	Services	January	50	50
2018	A	XX	Services	February	65	115
2018	B	ZZ	Services	March	45	160
2018	A	YY	Retail	January	21	21
2019	B	XX	Retail	February	45	66
2018	B	YY	Retail	March	23	89
2018	B	ZZ	Equipment	January	12	12
2018	C	ZZ	Equipment	February	55	67
2019	C	YY	Equipment	March	66	133

[Anonymous]

Adding to that I am using lots of slicers just the normal oob ones but Cat,Month,category etc..

[Zubair_Muhammad · Accepted solution]

@Anonymous

I think that whatever Column you use as a slicer would need to be part of ALLEXCEPT function. For example if you use region as a SLICER

Then Measure would be

Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ,Targets[Region]),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)

[Anonymous]

Great just updated my report and that dose seem to be the case thanks for all of your help

[Anonymous]

Thanks that works, starting the under stand Dax better now!