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 Guys I have a problem I am unable to solve.
I am trying to get an aggregated value for the week that is monday - sunday.
How am I able to aggregate it so it shows just for the last day of the week.
sample table:
Assuming by week is Monday - Sunday. Please I want to aggregate all quantities for each week and show it on the weekend date (Sunday of each week which is the max date)
Category | Date | Qty | Expected result |
a | 1/3/2022 | 100 | - |
a | 1/4/2022 | 150 | - |
a | 1/5/2022 | 50 | - |
a | 1/6/2022 | 300 | - |
a | 1/7/2022 | 100 | - |
a | 1/8/2022 | 25 | - |
a | 1/9/2022 | 0 | 725 |
Solved! Go to Solution.
Hi @yve214 ,
How about this:
Here the DAX code for the measure:
WeekTotalMeasure = VAR _currentWeek = WEEKNUM ( MAX ( Table[Date] ), 2 ) RETURN IF ( WEEKDAY ( MAX ( Table[Date] ), 2) = 7, CALCULATE ( SUM ( Table[Qty] ), ALL ( Table), WEEKNUM ( Table[Date] ) = _currentWeek ), BLANK() )
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thank you very much. It worked, I think I was using the wrong date table so it was giving me different aggregates.
@Icey thank you so much for verifying. I had to look at the query again with a fresh pair of eyes
Thank you so much for your help. I was able to get an aggregated sum for sundays but it wasnt the actual sum for each week (monday through sunday). Please what can I adjust?
Category | Date | Qty | Expected result |
a | 1/3/2022 | 100 | - |
a | 1/4/2022 | 150 | - |
a | 1/5/2022 | 50 | - |
a | 1/6/2022 | 300 | - |
a | 1/7/2022 | 100 | - |
a | 1/8/2022 | 25 | - |
a | 1/9/2022 | 0 | 725 |
a | 1/10/2022 | 101 | - |
a | 1/11/2022 | 85 | - |
a | 1/12/2022 | 100 | - |
a | 1/13/2022 | 150 | |
a | 1/14/2022 | 100 | - |
a | 1/15/2022 | 55 | - |
a | 1/16/2022 | 0 | 591 |
Hi @yve214 ,
Have you created a measure, not a column, based on @tackytechtom 's expression? Based on my test, it can give you the expected result.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @yve214 ,
How about this:
Here the DAX code for the measure:
WeekTotalMeasure = VAR _currentWeek = WEEKNUM ( MAX ( Table[Date] ), 2 ) RETURN IF ( WEEKDAY ( MAX ( Table[Date] ), 2) = 7, CALCULATE ( SUM ( Table[Qty] ), ALL ( Table), WEEKNUM ( Table[Date] ) = _currentWeek ), BLANK() )
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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 |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |