Power BI - Running total column with many filters

0
votes

I have table "Sales" (fields: Product, Country, Date, Sales) with monthly sales across many products and countries. Also I have tables with calendar, list of products, list of counties that are linked with this table. I want to add column to "Sales" with running total sales across each Product/Country, see the field with desired result "Running total".

I tried to use YTD = TOTALYTD(SUM(Sales[Sales]); Calendar[Date]) but it didn't work. I think I need to use filters in TOTALYTD function, but I also didn't manage to understand how. Can you suggest to me a right solution to my case?

Table "Sales"

3
powerbi

3 Answers

1
votes

I was suggested to use this code

Column = SUMX ( FILTER ( Sales, Sales[Product] = EARLIER ( Sales[Product] ) && Sales[Country] = EARLIER ( Sales[Country] ) && Sales[Date] <= EARLIER ( Sales[Date] ) && YEAR ( Sales[Date] ) = YEAR ( EARLIER ( Sales[Date] ) ) ), Sales[Sales] )

It worked.

0
votes

I partially coped with my issue by creating set of measures for each combination of product and country:

A_US = TOTALYTD(SUM(Sales[Sales]);'Calendar'[Date];FILTER(All(Sales);Sales[Product]="A"&&Sales[Country]="US"))

A_Canada = TOTALYTD(SUM(Sales[Sales]);'Calendar'[Date];FILTER(All(Sales);Sales[Product]="A"&&Sales[Country]="Canada"))

and so on. But what if i have 100 products and 30 countries? I think I need to create a column "Running total" in "Sales" that calculates running total for each product and aech country.

0
votes

The problem of the TOTALYTD function is that it takes only one filter. The tricks is to use the filter function like you do on the second response.

To use only one column for all product and country you have to get the context of the current row . To achieve this you have the function earlier in dax.

Here the documentation about earlier : https://docs.microsoft.com/en-us/dax/earlier-function-dax

The column need to be build with this expression :

TOTALYTD(SUM(Sales[Sales]),'Calendar'[Date],filter(Sales,and(Sales[Country]=EARLIER(sales[Country]),sales[Product] = EARLIER(sales[Product]))))