Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I'm facing difficulties calculating the sum of a column where the time boundaries of the sum are determined via a slicer:
The value I should get is the sum of 'Quantité N' from the lowest date up to the date determined by the slicer minus 1 year.
The current formula is the following:
So this formula gives me a result that i know is wrong because It does not stop the sum it should and when I use this formula
Solved! Go to Solution.
Hi @ReportingGB11 ,
We can create a measure to meet your requirement.
1. Firstly, we need to delete the relationship between SPHStockPF table and Date table.
Create a date slicer using Date table.
2. Then we can create a measure.
Stock Physique N-1 V2 =
var _select = DATE(YEAR(MAX('Date'[Date]))-1,MONTH(MAX('Date'[Date])),DAY(MAX('Date'[Date])))
return
CALCULATE(SUM('SPHStockPF'[Quantité N]),FILTER('SPHStockPF','SPHStockPF'[Posting Date]>=DATE(2019,1,10) && 'SPHStockPF'[Posting Date]<=_select))
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @ReportingGB11 ,
We can create a measure to meet your requirement.
1. Firstly, we need to delete the relationship between SPHStockPF table and Date table.
Create a date slicer using Date table.
2. Then we can create a measure.
Stock Physique N-1 V2 =
var _select = DATE(YEAR(MAX('Date'[Date]))-1,MONTH(MAX('Date'[Date])),DAY(MAX('Date'[Date])))
return
CALCULATE(SUM('SPHStockPF'[Quantité N]),FILTER('SPHStockPF','SPHStockPF'[Posting Date]>=DATE(2019,1,10) && 'SPHStockPF'[Posting Date]<=_select))
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Thanks a lot for the help
@ReportingGB11 , Try like
Year behind Sales = CALCULATE(SUM(SPHStockPF[Quantité N]),dateadd('Date'[Date],-1,Year),'Date'[Date]>= DATE(2018,12,31))
Hope you need data > jan 2018
@amitchandak and @parry2k , many thanks for your replies.
However, I cannot display the solution provided by @amitchandak, the DATEADD function shows an error: It "expects a contiguous selection ....". Yet the Date table on which the DATEADD function is performed is a calendar table
@ReportingGB11 if your goal is to get last year value, you can use many time intelligence functions like PARALLERPERIOD, SAMEPERIODLASTYEAR or DATESADD
for example
PY = CALCULATE ( [Your Measure], DATEADD ( DateTable[Date], -1, YEAR ) )
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.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |