Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Everyone,
I'm new to PowerBI and trying to look for a DAX formula for the cumulative value till a certain month.
I have a data till June 2021 but I want to show the cumulative value till May 2020 only.
I'm using the following formula which is showing the all values
Cumulative Earned Labor Units =
CALCULATE(
[Planned Cost],
FILTER(
ALL('Calendar'[Date]),
'Calendar'[Date]<=MAX('Calendar'[Date]
)
))
Could somebody help me to show the cumulative value till May 2020 only?
Your help is appreciated.
Thanks and Regards,
Nilesh Jain
Solved! Go to Solution.
Thanks Parvinder for you help.
2Cumulative Planned Labor Units =
VAR __stopDate = DATE ( 2020, 05, 29 )
RETURN
CALCULATE (
SUMX (
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
),
[Planned Cost]
),
KEEPFILTERS ( 'Calendar'[Date] <= __stopDate )
)
The above code sent by you did work. I really appreciate your help.
Please try this expression
Cumulative Earned Labor Units =
VAR __thismax =
MAX ( 'Calendar'[Date] )
VAR __total =
CALCULATE (
[Planned Cost],
FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= __thismax )
)
RETURN
IF ( __thismax <= DATE ( 2020, 5, 30 ), __total )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for your help, Pat.
However, the code is not working and I'm getting the following error.
Also, I changed _thisdate to _thismax but then I'm not able to add the measure under the value/line value section of any visualization.
I fixed the original reply. __thisdate should have been __total.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Great.
It did work but I can see the value till 2019 only despite we have (2020,05,30)
I tried the same with (2021,05,30) and I can see the value till 2020. Is this normal?
Please let me know.
Also, I appreciate your quick reply, Pat.
Regards,
Nilesh Jain
Since you are using the Year column in your visual, use DATE(2020, 12, 31).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@jnnilesh29 you can also try something like this
Cumulative Earned Labor Units =
VAR __stopDate = DATE ( 2020, 12, 31 ) --give the date where you want to stop the total
RETURN
CALCULATE (
CALCULATE (
[Planned Cost],
FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
),
'Calendar'[Date] <= __stopDate
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi parry2k,
Thanks, I did work and now the cumulative value is correctly reflected.
However, in the visualization a straight line is seen after the stop date.
My stop date is May 29, 2020.
Is there a way we stop the line after the stop date?
I'm looking forward to hearing from you.
@jnnilesh29 I don't expect it to continue and should stop at May 05th, 2020. Something else is going on there, can you share pbix file, remove any sensitive information before sharing.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@jnnilesh29 try one change and if it doesn't work, send pbix
Cumulative Earned Labor Units =
VAR __stopDate = DATE ( 2020, 12, 31 ) --give the date where you want to stop the total
RETURN
CALCULATE (
CALCULATE (
[Planned Cost],
FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
),
KEEPFILTERS ( 'Calendar'[Date] <= __stopDate )
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi Parvinder,
The line still continues beyond the stop date.
I have emailed you the pdix file.
I'm looking forward to hearing from you.
Regards,
Nilesh Jain
@jnnilesh29 no problem, haven't received yet. Will take a look as soon as I have it.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks Parvinder for you help.
2Cumulative Planned Labor Units =
VAR __stopDate = DATE ( 2020, 05, 29 )
RETURN
CALCULATE (
SUMX (
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
),
[Planned Cost]
),
KEEPFILTERS ( 'Calendar'[Date] <= __stopDate )
)
The above code sent by you did work. I really appreciate your help.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |