Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChoiJunghoon
Helper III
Helper III

[DAX] Cumulative Average with DAX

Can you save me ?

Dax.png

 

I want to calulate culmulative average in DAX ? 

 

Can you make the "orange color table " by using Dax ? 

 

 

IDDate

A2022-07-10
B2022-07-10
C2022-07-15
D2022-07-15
E2022-07-15
F2022-07-15
G2022-07-20
H2022-07-21
I2022-07-21
J2022-07-24
K2022-08-10
L2022-08-11
M2022-08-15
N2022-08-20
O2022-08-20
P2022-08-20
Q2022-08-20
R2022-09-10
S2022-09-10
T2022-09-20
U2022-09-20
V2022-09-30
W2022-09-30
X2022-10-05
Y2022-10-10
Z2022-10-20

 

TableB 

CategoryApplyDateGap

xxx2022-07-180.3
yyy2022-07-300.1
zzz2022-08-180.7
aaa2022-08-310.9

 

Table C (calendar )

DateMonth

2022-07-017
2022-07-027
2022-07-037
2022-07-047
2022-07-057
2022-07-067
2022-07-077
2022-07-087
2022-07-097
2022-07-107
2022-07-117
2022-07-127
2022-07-137
2022-07-147
2022-07-157
2022-07-167
2022-07-177
2022-07-187
2022-07-197
2022-07-207
2022-07-217
2022-07-227
2022-07-237
2022-07-247
2022-07-257
2022-07-267
2022-07-277
2022-07-287
2022-07-297
2022-07-307
2022-07-317
2022-08-018
2022-08-028
2022-08-038
2022-08-048
2022-08-058
2022-08-068
2022-08-078
2022-08-088
2022-08-098
2022-08-108
2022-08-118
2022-08-128
2022-08-138
2022-08-148
2022-08-158
2022-08-168
2022-08-178
2022-08-188
2022-08-198
2022-08-208
2022-08-218
2022-08-228
2022-08-238
2022-08-248
2022-08-258
2022-08-268
2022-08-278
2022-08-288
2022-08-298
2022-08-308
2022-08-318
2022-09-019
2022-09-029
2022-09-039
2022-09-049
2022-09-059
2022-09-069
2022-09-079
2022-09-089
2022-09-099
2022-09-109
2022-09-119
2022-09-129
2022-09-139
2022-09-149
2022-09-159
2022-09-169
2022-09-179
2022-09-189
2022-09-199
2022-09-209
2022-09-219
2022-09-229
2022-09-239
2022-09-249
2022-09-259
2022-09-269
2022-09-279
2022-09-289
2022-09-299
2022-09-309
2022-10-0110
2022-10-0210
2022-10-0310
2022-10-0410
2022-10-0510
2022-10-0610
2022-10-0710
2022-10-0810
2022-10-0910
2022-10-1010
2022-10-1110
2022-10-1210
2022-10-1310
2022-10-1410
2022-10-1510
2022-10-1610
2022-10-1710
2022-10-1810
2022-10-1910
2022-10-2010
2022-10-2110
2022-10-2210
2022-10-2310
2022-10-2410
2022-10-2510
2022-10-2610
2022-10-2710
2022-10-2810
2022-10-2910
2022-10-3010
2022-10-3110

 

 

 

MonthMonthQtyCulmulative QtyCulmulative SUMCulmulate Avg.
710101.20.12
87176.80.40
962318.80.82
1032624.80.95
1 ACCEPTED SOLUTION
magsod
Solution Supplier
Solution Supplier

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:
magsod_0-1668154122548.png

 

 

View solution in original post

4 REPLIES 4
magsod
Solution Supplier
Solution Supplier

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:
magsod_0-1668154122548.png

 

 

You are my super hero!! 

wdx223_Daniel
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors