topic Re: Cummulative Total Quanity with two parametres - showing constant line in DAX Commands and Tips

Cummulative Total Quanity with two parametres - showing constant line

gumis_rulez — Tue, 20 Aug 2024 11:54:25 GMT

Hi,

I am using two parameters: year and month.

After selecting year and month the graph shows [Total Quantity] for all data up to the choosen year and month. It works perfectly with [Total Quantity]. But not as perfect with [Cummulative Total Quantity (P)].
As shown on the graph below, the cummulative total quantity measure works as intended, it calculates the cummulative up to the choosen month/year but after, it show constant line. How to change this measure to get rid off this constant line?

The measure [Total Quantity Cummulative (P)] as follows:

Cummulative Total Quantity (P) = VAR ChoosenYearTest = [Selected Year Value] VAR ChoosenMonthTest = [Selected Month Value] RETURN CALCULATE( [Total Quantity], FILTER( ALL('Calendar'), ( 'Calendar'[Year_ID] < ChoosenYearTest || ( 'Calendar'[Year_ID] = ChoosenYearTest && 'Calendar'[Month_ID] <= ChoosenMonthTest ) ) && 'Calendar'[Transaction_Date] <= MAX('Calendar'[Transaction_Date]) ) )

File:

https://drive.google.com/file/d/1Nhn5iP5KhCe_A0KchZCdOJq5r82HcZFH/view?usp=sharing

Thank You.

Re: Cummulative Total Quanity with two parametres - showing constant line

Jihwan_Kim — Tue, 20 Aug 2024 13:35:13 GMT

Hi,

Please try something like below if it suits your requirement.

Cummulative Total Quantity (P) = VAR ChoosenYearTest = [Selected Year Value] VAR ChoosenMonthTest = [Selected Month Value] RETURN IF ( NOT ISBLANK ( [Total Quantity] ), CALCULATE ( [Total Quantity], FILTER ( ALL ( 'Calendar' ), ( 'Calendar'[Year_ID] < ChoosenYearTest || ( 'Calendar'[Year_ID] = ChoosenYearTest && 'Calendar'[Month_ID] <= ChoosenMonthTest ) ) && 'Calendar'[Transaction_Date] <= MAX ( 'Calendar'[Transaction_Date] ) ) ) )

Re: Cummulative Total Quanity with two parametres - showing constant line

gumis_rulez — Tue, 20 Aug 2024 14:03:37 GMT

Unfortunately, not working.

Re: Cummulative Total Quanity with two parametres - showing constant line

gumis_rulez — Tue, 20 Aug 2024 16:48:35 GMT

Cummulative Total Quantity (P) = VAR ChoosenYearTest = [Selected Year Value] VAR ChoosenMonthTest = [Selected Month Value] RETURN IF ( MAX('Calendar'[Year_ID]) < ChoosenYearTest || ( MAX('Calendar'[Year_ID]) = ChoosenYearTest && MAX('Calendar'[Month_ID]) <= ChoosenMonthTest ), CALCULATE( [Total Quantity], FILTER( ALL('Calendar'), ( 'Calendar'[Year_ID] < ChoosenYearTest || ( 'Calendar'[Year_ID] = ChoosenYearTest && 'Calendar'[Month_ID] <= ChoosenMonthTest ) ) && 'Calendar'[Transaction_Date] <= MAX('Calendar'[Transaction_Date]) ) ), BLANK() )