Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Product | Country | Date | Sales, tons | Running total, tons |
A | US | Jan-18 | 10 | 10 |
A | US | Aug-18 | 5 | 15 |
A | US | Feb-19 | 20 | 20 |
A | US | May-19 | 5 | 25 |
A | Canada | Mar-18 | 1 | 1 |
A | Canada | Dec-18 | 3 | 4 |
A | Canada | Feb-19 | 7 | 7 |
A | Canada | Jun-19 | 10 | 17 |
B | UK | Feb-18 | 4 | 4 |
B | UK | Sep-18 | 1 | 5 |
B | UK | Jan-19 | 8 | 8 |
B | UK | Oct-19 | 4 | 12 |
B | Germany | Mar-18 | 15 | 15 |
B | Germany | Nov-18 | 3 | 18 |
B | Germany | Feb-19 | 7 | 7 |
B | Germany | May-19 | 1 | 8 |
C | Japan | Mar-18 | 20 | 20 |
C | Japan | Jul-18 | 11 | 31 |
C | Japan | Oct-19 | 2 | 2 |
C | Japan | Nov-19 | 5 | 7 |
C | Korea | Feb-18 | 5 | 5 |
C | Korea | Mar-18 | 7 | 12 |
C | Korea | Jun-19 | 1 | 1 |
C | Korea | Jul-19 | 9 | 10 |
Solved! Go to Solution.
@Anonymous
You may refer to the DAX below.
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] )
@Anonymous
You may refer to the DAX below.
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] )
Thanks, your solution was successful.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |