Cumulative dax query help

[GuestUser]

Hi
We are using power bi with ssas tabular model using connect live option.
Need help in cumulative column
Report format is as below

Matrix view

Year. 2018. 2019
Item. Trxcount. Cumulative Trx...... Cumul
A. 10. 10. 11. 11
B. 20. 30. 22. 33
C. 30. 60. 33. 66

Need help is writing dax query for cumulative measure

[mwegener]

Hi @GuestUser ,

try this.

Cumulative Trx = 
CALCULATE(
	[Trxcount],
	FILTER(
		ALLSELECTED('Item'[Item]),
		ISONORAFTER('Item'[Item], MAX('Item'[Item]), DESC)
	)
)

PBIX

[GuestUser]

Hi @mwegener 

While using MAX function , i get the error ...max function can work only on numbers or dates and not string data types

I am using Connect Live (SSAS Tabular)

just for trying....I checked on pbix file where  import method was used ...it works there...

Any idea ..why is it so??

Any suggestions on how to implement in connect live?

[mwegener]

Hi @GuestUser ,

I am currently not working with SSAS Tabular, but it is documented in the DAX Guide as follows.

Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.

https://dax.guide/max/

Which version are you using?

[GuestUser]

Hi @mwegener 

Ssas 2016

Report server - Jan 2020 Version

[mwegener]

Hi @GuestUser ,

the same problem is described here.

https://community.powerbi.com/t5/Service/Running-Total-Issue/td-p/964702

Unfortunately, the SELECTEDVALUE solution is only available from SSAS 2017.

https://dax.guide/selectedvalue/

[GuestUser · Accepted solution]

Hi @mwegener 

@Thanks for your inputs!!

Used below formula(similar)..replaced selected value by hasonevalue and it worked..

Thanks !!

Measure =
CALCULATE (
    [Sales],
    FILTER (
        ALLSELECTED ( 'Table'[Category] ),
        'Table'[Category] <= SELECTEDVALUE ( 'Table'[Category] )
    )
)

[v-eachen-msft]

Hi @GuestUser ,

I am glad that your problem has been resolved, you could accept your own reply to close the case.

[GuestUser]

hi,

facing some strange behaviour

the below formula works

Cumulative = calculate( table[total sales],

filter

(allselected(item[item_name]),

item[item_name]<=if(hasonevalue(item[item_name]),values(item[item_name])

))

when i dont put any filter condition on total sales..cumulative value is correct...but it shows item with no sales as well

So when i put filter condition as total sales is not blank in report ,

then the cumulative value shows wrong value..it shows correct for 2 records..then the third record..it shows the same value as total sales

Any suggestions pls?

[mwegener]

Hi @GuestUser,

can you provide a screenshot?

[GuestUser]

[GuestUser]

Hi @mwegener

First screenshot is without any filter condition added -- it giving correct result

Second one - Added filter condition on filter pane ..like trx count is not blank...it gives incorrect result

basically i do not need those items in the report with trx count blank

Any suggestions pls

[mwegener]

Hi @GuestUser ,

can you post the DAX formula for both measures?

[GuestUser]

Hi,

1) Cumulative = calculate( table[trx count],

filter

(allselected(item[item_name]),

item[item_name]<=if(hasonevalue(item[item_name]),values(item[item_name])

))

2) Trx Count = sum(table.item_count)

[GuestUser]

Hi @mwegener ,

1) Cumulative = calculate( table[trx count],

filter

(allselected(item[item_name]),

item[item_name]<=if(hasonevalue(item[item_name]),values(item[item_name])

))

2) Trx Count = sum(table.item_count)

[mwegener]

Hi @GuestUser ,

I don't understand why the "cumulative count" for some items in screenshot is 0.

Here I would expect the value of the previous item.

[GuestUser]

Hi @mwegener ,

Thanks for your inputs!!

Changed formula for Trx Count 

Trx_Count = if(sum(Table[Item_count]) = blank(),0,sum(table[item_count])) ----  Shows 0 instead of blank

And Cumulative values show..previous value where Trx_Count is 0

But still I am not able to get rid of records having Trx_Count=0 , when i apply filter..it gives incoorect cumulative value...as posted in previous screenshot

[mwegener]

Hi @GuestUser 

I have created the following sample PBIX.
Why are these rows in your screenshot 0?

[Mariusz]

Hi @GuestUser 

Try this pattern.

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 Read this article for more info 
https://www.daxpatterns.com/cumulative-total/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[GuestUser]

Hi @Mariusz 

have tried this and its giving same value for all items same like transaction count

need the cumulative value item wise

any suggestion pls

[v-eachen-msft]

Hi @GuestUser ,

You need to group by Year with ALLEXCEPT() function.

Refer to the following DAX:

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Year] ),
        'Table'[Value] <= MAX ( 'Table'[Value] )
    )
)

Here is the result.