Cumulative sales by week number and day of week

[alexa_0028]

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 🙂

[alexa_0028 · Accepted 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.

[Icey]

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

[ALLUREAN]

Hi, @alexa_0028 

Please check this example:

https://www.dropbox.com/s/wdizzoqkp4g9i28/Cumulative%20sales%20by%20week%20number%20and%20day%20of%2...

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://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[alexa_0028]

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

test_Cumulative_Sales_1 =

CALCULATE(

[Sales],

FILTER(

ALLSELECTED('Calendar'),

WEEKNUM('Calendar'[Date],2) <= WEEKNUM(MAX('Calendar'[Date]),2)

&& 'Calendar'[day_of_week_code] <= MAX('Calendar'[day_of_week_code])

))

[alexa_0028]

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?

[alexa_0028]

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:

test =

DASX:

CALCULATE(

[Sales],

FILTER(

ALL('Calendar'),

'Calendar'[D. Week]<=MAX('Calendar'[D. Week])))

[Ashish_Mathur]

Hi,

Assuming:

1. D. Week and D. Day of week are from the Calendar Table; and
2. There is a relationship from the Date column of your Data Table to the Date column of the Calendar Table, write this measure

Cumulative sales = calculate([sales],datesytd(calendar[date],"31/12"))

Hope this helps.

[alexa_0028]

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.

[Ashish_Mathur]

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.

[alexa_0028]

Hi @Ashish_Mathur 
The calendar table does have date column with type date/time

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[ALLUREAN]

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://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/