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.
Hello,
I have this table in Excel It contains
1. Date
2. Col1 is a fixed column
3. Col3 is a fixed column
I want to compute Col2 and Col4
Starting with Col2
1. Col2 Starts with same value in B2 (because nothing is in previous values)
2. Then in Month 2 , C3 = B3+C2 - D3 = 4
I know how to access previous value in DAX, but wont help much
CALCULATE(
sum(Table[Col1]),
PREVIOUSMONTH(Table[Date]),
)
Issue Persists in the steps below( in Month3) , now I want to use values that were computed in step above
How can I do it in DAX for the highlighted cells ?
Solved! Go to Solution.
Hi @myou,
It seems like a simple rolling total of [B] -[D] fields with condition that less than or equal to the current row. You can create calculated columns with below formulas to achieve your requirement:
C =
SUMX (
FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) ),
[B] - [D]
)
E = Table[C]-Table[D]
Regards,
Xiaoxin Sheng
Hi @myou,
It seems like a simple rolling total of [B] -[D] fields with condition that less than or equal to the current row. You can create calculated columns with below formulas to achieve your requirement:
C =
SUMX (
FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) ),
[B] - [D]
)
E = Table[C]-Table[D]
Regards,
Xiaoxin Sheng
@myou , prefer to use a date calendar and take month from that
Cumm =
CALCULATE(SUM(Table[B]),filter(date,date[date] <=max(Table[Date]))) // Or the first value of B
+ CALCULATE(SUM(Table[C]),filter(date,date[date] <=max(Table[Date])))
-CALCULATE(SUM(Table[D]),filter(date,date[date] <=max(Table[Date])))
All are cumulative totals. The one from B can the first value
@myou , updated the post. It needs be column D
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |