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.
Hi All,
Can you please help me to achieve a logic to calculate the Final Value(column) logic by using DAX .
My case is to have "0' for January 2022 and for Feb the value should be Jan 2022 and For April the value should be sum(Jan,feb,Mar) and for May it should be sum(Jan,Feb,mar,Apr).
I am creating a measure , For which I have created "start range" and "End Range" measures to get this Final Value column to be created. Please help
Month | Start Range | EndRange | Attrition | Final Value |
1/1/22 | 1/1/22 | 1/31/22 | 10 | 0 |
2/1/22 | 1/1/22 | 1/31/22 | 20 | 10 |
3/1/22 | 1/1/22 | 2/28/22 | 30 | 30 |
4/1/22 | 1/1/22 | 3/31/22 | 40 | 60 |
5/1/22 | 1/1/22 | 4/30/22 | 50 | 100 |
6/1/22 | 1/1/22 | 5/31/22 | 60 | 150 |
7/1/22 | 1/1/22 | 6/30/22 | 70 | 200 |
Thanks in advance,
Solved! Go to Solution.
Hello,
You can use the following measure to calculate rolling sum for previous months.
Hi @Anonymous
Please try this measure
measure =
SUMX (
FILTER ( ALL ( 'table' ), 'table'[MonthStart] < MAX ( 'table'[MonthStart] ) ),
[Attrition]
)
Regards,
OliT
Hello,
You can use the following measure to calculate rolling sum for previous months.
Thanks for the quick reply,
Unfortunately I cannot use "Attrition" in the SUM because it was derived using the below logic measures. Please help me how to crack this.
@ddpl - Hi, Looking for your reply/ any alternate solution. Thank you in advance
@Anonymous ,Hey bro
You don't need other column to get running total like Start or End Range
Just create a measure as below and put it along with Date and Attrition in Table...
Your Need =
VAR need = CALCULATE(SUM('Table'[Attrition]), FILTER(ALLSELECTED('Table'), 'Table'[Date]<MAX('Table'[Date])))
RETURN IF(need=BLANK(), 0, need)
Hi @ddpl ,
Thanks for the quick reply,
Unfortunately I cannot use "Attrition" in the SUM because it was derived using the below logic measures. Please help me how to crack this.
RETURN IF(need=BLANK(), 0, need)
@Anonymous , You can join date with date table and can use time intelligence
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |