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.
Hei all,
I have a table similar this:
However I need to buil using dax a table similar this:
I need to add the rows with data is missing and after to add the column with sum cumulative.
Is it possible to build this?
Regards,
Solved! Go to Solution.
Hi @Anonymous ,
Try the below steps:
Step1,create date table and enter product table:
Table2 = CALENDAR("2020,1,1","2020,1,5")
Step 2,crossjoin the two table:
Table4 = CROSSJOIN(Table2,Table3)
Step3,get the base table value:
value = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[Date]=Table4[Date]&&'Table'[Product]=Table4[Product]))
Step 4,get the sum value the date<= earlier(date)
Column = CALCULATE(SUM(Table4[value]),FILTER(ALL(Table4),Table4[Product]=EARLIER(Table4[Product])&&Table4[Date]<=EARLIER(Table4[Date])))
Output result:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @Anonymous ,
Try the below steps:
Step1,create date table and enter product table:
Table2 = CALENDAR("2020,1,1","2020,1,5")
Step 2,crossjoin the two table:
Table4 = CROSSJOIN(Table2,Table3)
Step3,get the base table value:
value = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[Date]=Table4[Date]&&'Table'[Product]=Table4[Product]))
Step 4,get the sum value the date<= earlier(date)
Column = CALCULATE(SUM(Table4[value]),FILTER(ALL(Table4),Table4[Product]=EARLIER(Table4[Product])&&Table4[Date]<=EARLIER(Table4[Date])))
Output result:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
@Anonymous , I think if you use a date table joined with date of your table. Also you need to use date from date table in visual
Cumm Sales = CALCULATE(SUM(Table[Values]),filter(allselected(date),date[date] <=max(date[Date])))
If needed add +0
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.
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |