topic Re: cumulative with filter in Report Server

cumulative with filter

LeaRupnik — Thu, 27 May 2021 07:46:32 GMT

I have a problem calculating the cumulative. I would like it to be calculated according to the filter, which means that if I chose the month of January, I find it good if I choose the month of February, I should get sumo January + February, then if I choose March, I should get sumo January + February + March

now i have a dax formula like this:

KUM REAL:= var leto = MAX('planir planirano'[mesec])
return
CALCULATE([REAL],
ALLSELECTED('planir planirano'), 'planir planirano'[mesec]<= leto,
VALUES('planir planirano'[Sistem Impol]),
VALUES('planir planirano'[Naziv firma]),
VALUES('planir planirano'[Naziv programa]),
VALUES('planir planirano'[Naziv programske enote]),
VALUES('planir planirano'[int_ext]),
VALUES('planir planirano'[vrsta_mereid]),
VALUES('planir planirano'[leto]))

vizualization like this:

please help

thx Lea

Re: cumulative with filter

FarhanAhmed — Thu, 27 May 2021 09:36:40 GMT

You can use TotalYTD to create Cumulative measure based on YTD Values.

YTD = TOTALYTD([MeasureName],’Calendar'[Date])

Ref: https://docs.microsoft.com/en-us/dax/totalytd-function-dax

Ref 2 : https://www.sqlbi.com/blog/marco/2018/08/10/the-hidden-secrets-of-totalytd/

Re: cumulative with filter

Icey — Mon, 31 May 2021 07:22:36 GMT

Hi LeaRupnik ,

Try this:

Measure = CALCULATE ( SUM ( 'Table'[Sales] ), DATESBETWEEN ( Dates[Date], DATE ( YEAR ( MAX ( Dates[Date] ) ), 1, 1 ), MAX ( Dates[Date] ) ) )

Or this:

Measure = CALCULATE ( SUM ( 'Table'[Sales] ), DATESBETWEEN ( Dates[Date], STARTOFYEAR ( Dates[Date] ), MAX ( Dates[Date] ) ) )

Best Regards,

Icey

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