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 am looking to calculate the difference of the SUM of values between the LASTDATE rows, and the rows from the 2nd most recent date (note that the dataset dates do not consist of consecutive days).
The calculation I have for SUM Last Date is working:
Solved! Go to Solution.
Hi @mwc
First, don't convert the date to text, lose the FORMAT(). I'm assuming your date column is of Date type of course:
2nd Last Date V2 = CALCULATE ( MAX ( Sheet1[Date] ), FILTER ( Sheet1, 'Sheet1'[Date] <> MAX ( Sheet1[Date] ) ) )
and then:
SUM 2ND LAST DATE = VAR SecondLast_ = [2nd Last Date V2] // Your measure V2. Best practice is to not use table name with measures RETURN CALCULATE ( SUM ( Sheet1[NET_OPEN] ), Sheet1[Date] = SecondLast_ )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi! I have this kind of problem in Power BI. I have a change number and status. As long as the last User Name is Change Analyst - Manila, I need to get the date difference from the previous date (second to the last date - this will be my start date). My end date would be the date from Change Analyst - Manila.
Hi @mwc
First, don't convert the date to text, lose the FORMAT(). I'm assuming your date column is of Date type of course:
2nd Last Date V2 = CALCULATE ( MAX ( Sheet1[Date] ), FILTER ( Sheet1, 'Sheet1'[Date] <> MAX ( Sheet1[Date] ) ) )
and then:
SUM 2ND LAST DATE = VAR SecondLast_ = [2nd Last Date V2] // Your measure V2. Best practice is to not use table name with measures RETURN CALCULATE ( SUM ( Sheet1[NET_OPEN] ), Sheet1[Date] = SecondLast_ )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi guys, if i want to do the same but with the third and fourth date what can i do?
Thanks
Hi @AlB ,
Your formula seems to be working for my file , but when i put in my matrix the columns it is showing only the Last month amount.
If i reamove the split by year it shows the sum of the multiple years and the delta as last to .
IS there a way around it ?
Thanks
Alex
I'm not sure I understand the question. Can you show an example to illustrate the problem (ideally on a .pbix)?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Great job!!! It works perfectly.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |