[DAX] Cumulative Average with DAX

[ChoiJunghoon]

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

[magsod · Accepted solution]

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] )

    RETURN

        CALCULATETABLE (

            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:

[ChoiJunghoon]

You are my super hero!! 

[wdx223_Daniel]

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...