Forum Discussion
Helper III[DAX] Cumulative Average with DAX
Can you save me ?
I want to calulate culmulative average in DAX ?
Can you make the "orange color table " by using Dax ?
IDDate
| A | 2022-07-10 |
| B | 2022-07-10 |
| C | 2022-07-15 |
| D | 2022-07-15 |
| E | 2022-07-15 |
| F | 2022-07-15 |
| G | 2022-07-20 |
| H | 2022-07-21 |
| I | 2022-07-21 |
| J | 2022-07-24 |
| K | 2022-08-10 |
| L | 2022-08-11 |
| M | 2022-08-15 |
| N | 2022-08-20 |
| O | 2022-08-20 |
| P | 2022-08-20 |
| Q | 2022-08-20 |
| R | 2022-09-10 |
| S | 2022-09-10 |
| T | 2022-09-20 |
| U | 2022-09-20 |
| V | 2022-09-30 |
| W | 2022-09-30 |
| X | 2022-10-05 |
| Y | 2022-10-10 |
| Z | 2022-10-20 |
TableB
CategoryApplyDateGap
| xxx | 2022-07-18 | 0.3 |
| yyy | 2022-07-30 | 0.1 |
| zzz | 2022-08-18 | 0.7 |
| aaa | 2022-08-31 | 0.9 |
Table C (calendar )
DateMonth
| 2022-07-01 | 7 |
| 2022-07-02 | 7 |
| 2022-07-03 | 7 |
| 2022-07-04 | 7 |
| 2022-07-05 | 7 |
| 2022-07-06 | 7 |
| 2022-07-07 | 7 |
| 2022-07-08 | 7 |
| 2022-07-09 | 7 |
| 2022-07-10 | 7 |
| 2022-07-11 | 7 |
| 2022-07-12 | 7 |
| 2022-07-13 | 7 |
| 2022-07-14 | 7 |
| 2022-07-15 | 7 |
| 2022-07-16 | 7 |
| 2022-07-17 | 7 |
| 2022-07-18 | 7 |
| 2022-07-19 | 7 |
| 2022-07-20 | 7 |
| 2022-07-21 | 7 |
| 2022-07-22 | 7 |
| 2022-07-23 | 7 |
| 2022-07-24 | 7 |
| 2022-07-25 | 7 |
| 2022-07-26 | 7 |
| 2022-07-27 | 7 |
| 2022-07-28 | 7 |
| 2022-07-29 | 7 |
| 2022-07-30 | 7 |
| 2022-07-31 | 7 |
| 2022-08-01 | 8 |
| 2022-08-02 | 8 |
| 2022-08-03 | 8 |
| 2022-08-04 | 8 |
| 2022-08-05 | 8 |
| 2022-08-06 | 8 |
| 2022-08-07 | 8 |
| 2022-08-08 | 8 |
| 2022-08-09 | 8 |
| 2022-08-10 | 8 |
| 2022-08-11 | 8 |
| 2022-08-12 | 8 |
| 2022-08-13 | 8 |
| 2022-08-14 | 8 |
| 2022-08-15 | 8 |
| 2022-08-16 | 8 |
| 2022-08-17 | 8 |
| 2022-08-18 | 8 |
| 2022-08-19 | 8 |
| 2022-08-20 | 8 |
| 2022-08-21 | 8 |
| 2022-08-22 | 8 |
| 2022-08-23 | 8 |
| 2022-08-24 | 8 |
| 2022-08-25 | 8 |
| 2022-08-26 | 8 |
| 2022-08-27 | 8 |
| 2022-08-28 | 8 |
| 2022-08-29 | 8 |
| 2022-08-30 | 8 |
| 2022-08-31 | 8 |
| 2022-09-01 | 9 |
| 2022-09-02 | 9 |
| 2022-09-03 | 9 |
| 2022-09-04 | 9 |
| 2022-09-05 | 9 |
| 2022-09-06 | 9 |
| 2022-09-07 | 9 |
| 2022-09-08 | 9 |
| 2022-09-09 | 9 |
| 2022-09-10 | 9 |
| 2022-09-11 | 9 |
| 2022-09-12 | 9 |
| 2022-09-13 | 9 |
| 2022-09-14 | 9 |
| 2022-09-15 | 9 |
| 2022-09-16 | 9 |
| 2022-09-17 | 9 |
| 2022-09-18 | 9 |
| 2022-09-19 | 9 |
| 2022-09-20 | 9 |
| 2022-09-21 | 9 |
| 2022-09-22 | 9 |
| 2022-09-23 | 9 |
| 2022-09-24 | 9 |
| 2022-09-25 | 9 |
| 2022-09-26 | 9 |
| 2022-09-27 | 9 |
| 2022-09-28 | 9 |
| 2022-09-29 | 9 |
| 2022-09-30 | 9 |
| 2022-10-01 | 10 |
| 2022-10-02 | 10 |
| 2022-10-03 | 10 |
| 2022-10-04 | 10 |
| 2022-10-05 | 10 |
| 2022-10-06 | 10 |
| 2022-10-07 | 10 |
| 2022-10-08 | 10 |
| 2022-10-09 | 10 |
| 2022-10-10 | 10 |
| 2022-10-11 | 10 |
| 2022-10-12 | 10 |
| 2022-10-13 | 10 |
| 2022-10-14 | 10 |
| 2022-10-15 | 10 |
| 2022-10-16 | 10 |
| 2022-10-17 | 10 |
| 2022-10-18 | 10 |
| 2022-10-19 | 10 |
| 2022-10-20 | 10 |
| 2022-10-21 | 10 |
| 2022-10-22 | 10 |
| 2022-10-23 | 10 |
| 2022-10-24 | 10 |
| 2022-10-25 | 10 |
| 2022-10-26 | 10 |
| 2022-10-27 | 10 |
| 2022-10-28 | 10 |
| 2022-10-29 | 10 |
| 2022-10-30 | 10 |
| 2022-10-31 | 10 |
| Month | MonthQty | Culmulative Qty | Culmulative SUM | Culmulate Avg. |
| 7 | 10 | 10 | 1.2 | 0.12 |
| 8 | 7 | 17 | 6.8 | 0.40 |
| 9 | 6 | 23 | 18.8 | 0.82 |
| 10 | 3 | 26 | 24.8 | 0.95 |
To simplyfy the solution to you problem I would suggest to model your data with a new Fact table that combines the information in TableA with TableB.
So if the Date in TableA is on or after the ApplyDate in TableB you get the Category and corresponding Gap in separate columns in the new Fact table.
You can create this Fact table as a new calculated table with the following DAX expression:FactTable =GENERATEALL (TableA,VAR curDate =RELATED ( 'Calendar'[Date] )RETURNCALCULATETABLE (TableB,TableB[ApplyDate] <= curDate,REMOVEFILTERS ( 'Calendar' )))Once you have that table you create a relationship with FactTable[Date] and Calendar[Date].The three measures you need to create the required matrix will the be the following:Cumulative Qty =CALCULATE(DISTINCTCOUNT(FactTable[ID]),DATESYTD('Calendar'[Date]))Cumulative Sum =CALCULATE(SUM(FactTable[Gap]),DATESYTD('Calendar'[Date]))Cumulative Average =DIVIDE([Cumulative Sum],[Cumulative Qty])The result will be this:
4 Replies
To simplyfy the solution to you problem I would suggest to model your data with a new Fact table that combines the information in TableA with TableB.
So if the Date in TableA is on or after the ApplyDate in TableB you get the Category and corresponding Gap in separate columns in the new Fact table.
You can create this Fact table as a new calculated table with the following DAX expression:FactTable =GENERATEALL (TableA,VAR curDate =RELATED ( 'Calendar'[Date] )RETURNCALCULATETABLE (TableB,TableB[ApplyDate] <= curDate,REMOVEFILTERS ( 'Calendar' )))Once you have that table you create a relationship with FactTable[Date] and Calendar[Date].The three measures you need to create the required matrix will the be the following:Cumulative Qty =CALCULATE(DISTINCTCOUNT(FactTable[ID]),DATESYTD('Calendar'[Date]))Cumulative Sum =CALCULATE(SUM(FactTable[Gap]),DATESYTD('Calendar'[Date]))Cumulative Average =DIVIDE([Cumulative Sum],[Cumulative Qty])The result will be this:
ChoiJunghoonYou are my super hero!!
- wdx223_Daniel
Community Champion
where did the QTY and SUM come from? and what's your data structure?
- ChoiJunghoon
Thank you for your reply
This is excel file.
I simply expressed the direction I wanted in Excel as a table. It would be nice if the files were attached...
