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,
I want to calculate the cumulative sales over week and day of week.
Expected output:
Can someone please help me?
Thanks in advance 🙂
Solved! Go to Solution.
Thank you @amitchandak for helping me out here.
The issue was I have multiple years with same week number and day of week so I was not able to calculate the cumulative sales directly.
The approached followed was to first create the rank and then apply Cumulative Sales.
Refer this link for solution
https://www.dropbox.com/s/yhqg1xkaxr6fw7n/Cumm%20with%20week%20and%20weekday.pbix?dl=0
Thank you once again @amitchandak for your help, really appreciate it.
Thank you @amitchandak for helping me out here.
The issue was I have multiple years with same week number and day of week so I was not able to calculate the cumulative sales directly.
The approached followed was to first create the rank and then apply Cumulative Sales.
Refer this link for solution
https://www.dropbox.com/s/yhqg1xkaxr6fw7n/Cumm%20with%20week%20and%20weekday.pbix?dl=0
Thank you once again @amitchandak for your help, really appreciate it.
Hi @alexa_0028 ,
What is the structure of your two data tables? And how is the relationship between them established?
Could you share us some sample data for test? This way we can help you more directly without guessing your data structure and model composition.
Reference:
How to Get Your Question Answered Quickly - Microsoft Power BI Community
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Icey
Hi, @alexa_0028
Please check this example:
You just need to have a week number calculated - use like WEEKNUM('DateTable'[Date],2) in your date table in order to calculate it:
Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.
Appreciate your Kudos !!!
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/
Proud to be a Super User!
Hi @ALLUREAN
I tried the approach of weeknum, and I am still not getting expected result.
Highlighted in yellow are the wrong values but blue ones are right.
Please help me, I am using below DASX
Hi @ALLUREAN
Thanks for the reply.
How will the formula work as my Week/Day of week are from calendar table and the sum(sales) is from sales table.
What should i add to "YourTable" in the formula above?
Hi All,
I am trying the below formula but it is giving me cumulative sale over weeknumber only but i want both weeknumber and days.
Wrong Output:
Hi,
Assuming:
Cumulative sales = calculate([sales],datesytd(calendar[date],"31/12"))
Hope this helps.
Hi @Ashish_Mathur
Thanks for suggestion but this didnt work as my visual doesn't have date column,so I am seeing all blanks at the moment.
Hi,
It is not the visual but the Calendar Table which should have a Date column. If it does not, my solution will not work.
Hi,
Share the link from where i can download your PBI file.
Hi, @alexa_0028
You can try this using below steps.
1 - Add an order number. You can use RANKX function for this.
Rank = RANKX(YourTable,YourTable[D.Day of Week],,ASC,Dense)
2 - Add Cumulative (running total) using below formula
Cumulative Sales = CALCULATE ( SUM ( YourTable[Sales] ), ALLEXCEPT ( YourTable, YourTable[D.Week] ), YourTable[Rank] <= EARLIER ( YourTable[Rank] ) )
Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.
Appreciate your Kudos !!!
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/
Proud to be a Super User!
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 |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |