topic Re: [DAX] Cumulative Average with DAX in DAX Commands and Tips

[DAX] Cumulative Average with DAX

ChoiJunghoon — Fri, 11 Nov 2022 07:09:47 GMT

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

Re: [DAX] Cumulative Average with DAX

wdx223_Daniel — Fri, 11 Nov 2022 03:55:03 GMT

where did the QTY and SUM come from? and what's your data structure?

Re: [DAX] Cumulative Average with DAX

ChoiJunghoon — Fri, 11 Nov 2022 07:05:45 GMT

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

Re: [DAX] Cumulative Average with DAX

Anonymous — Fri, 11 Nov 2022 08:09:16 GMT

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:

Re: [DAX] Cumulative Average with DAX

ChoiJunghoon — Fri, 11 Nov 2022 13:27:43 GMT

You are my super hero!!