No interactive chart when to compute cumulative figure.

[lenaynar]

Hello respective team,

I have computed cumulative of quantity. I have developed two charts 1). Qantity by gender and 2) Qunatity by Month. When user clik on the Bar chart male there is no interactive cumulative data. Any advice that can do interactive chart when we do modeling using DAX function. Thanks!

Thanks!

[amitchandak · Accepted solution]

@lenaynar , Try like , measure like

CALCULATE(SUM(supermarket_sales[Quantity]),FILTER(all(supermarket_sales),supermarket_sales[Date]<=max(supermarket_sales[Date])))

or

CALCULATE(SUM(supermarket_sales[Quantity]),FILTER(allselected(supermarket_sales),supermarket_sales[Date]<=max(supermarket_sales[Date])))

or have a separate date table

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[lenaynar]

Hi @ValtteriN and @amitchandak 

Thank you so much. the dax function is working well. Thanks!

[lenaynar]

Hi @amitchandak and @ValtteriN 

Thanks you so much for your prompt help.

Here is my DAX:

Quanity_cumulative =

 CALCULATE(SUM(supermarket_sales[Quantity]),FILTER(all(supermarket_sales),supermarket_sales[Date]<=EARLIER(supermarket_sales[Date])))

the solution will I change my DAX coding from EARLIER to MAX function?

[amitchandak · Accepted solution]

@lenaynar , Try like , measure like

CALCULATE(SUM(supermarket_sales[Quantity]),FILTER(all(supermarket_sales),supermarket_sales[Date]<=max(supermarket_sales[Date])))

or

CALCULATE(SUM(supermarket_sales[Quantity]),FILTER(allselected(supermarket_sales),supermarket_sales[Date]<=max(supermarket_sales[Date])))

or have a separate date table

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[lenaynar]

Dear @amitchandak ,

The result become like this. it not cumulative aggregate by month and there is no interactive chart each other.

Quanity_cumulative =

 CALCULATE(SUM(supermarket_sales[Quantity]),FILTER(ALL(supermarket_sales),supermarket_sales[Date]<=MAX(supermarket_sales[Date])))

or 

Quanity_cumulative =

 CALCULATE(SUM(supermarket_sales[Quantity]),FILTER(ALLSELECTED(supermarket_sales),supermarket_sales[Date]<=MAX(supermarket_sales[Date])))

[ValtteriN · Accepted solution]

Hi @lenaynar,

If you want to your cumulative calculation to be on a YTD level the easiest way is to use DATESYTD. So e.g. Measure = CALCULATE(SUM(supermarket_sales[Quantity]),DATESYTD('Calendar'[Date]))

If this isn't the case and you want to have RT on a different context I recommend checking this article by SQLBI: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

Finally, if both of these patterns fail in your model there is a problem with the relationships between your fact table and [gender] column. In this case I recommend checking if there is an active relationship that can be used to determine sales quantity by gender. This is unlikely given your first picture, but it is a possibility. 

[amitchandak]

@lenaynar , Assume you have a table which contains both Gender and Date and create a cumulative like

It will not take any filter

Cumm = CALCULATE(SUM(Table[Amount]),filter(all('Table'),'Table'[date] <=max('Table'[date])))

it will take all filter

Cumm = CALCULATE(SUM(Table[Amount]),filter(all('Table'),'Table'[date] <=max('Table'[date])))

best one is to have separate table , Date table joined with your table, will take gender filter not date

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Also, check interactions 

How Interactions Work- Split Page using interactions to compare - https://youtu.be/GIfRKzhMaR4

Rolling Days Formula: https://youtu.be/cJVj5nhkKBw

Rolling Months Formula: https://youtu.be/GS5O4G81fww

[ValtteriN]

Hi,

What kind of calculation you are using for your cumulative calculation? The issue here is that the filter context (male) has no effect on your calculation and thus the result won't change. To solve the issue you need to change your dax or the relationships in your data model.