Weekly Commulative some divided over 4 weeks in table

[chlimaye]

Hi All,

I want to achieve one condition below for weekly commulative sum of sales.

i want data to be stored in above manner for weekly sales.

My cummulative dax function working fine but it only works as week completed and its starts showing the value,  then for next 7 days no data untill it completed the week.

Also i want show data untill previous working day for example.

Today is 29 Jan and my data should shown in last week only untill 28 Dec with cummulative manner.

VAR _MaxWeekEndDate = MAX(DEMAND_DAY_SUMMARY[Weekend Date])

Var Total=

CALCULATE(

    SUM('DEMAND_DAY_SUMMARY'[BASE_ACTUAL_DELIVERED_QUANTITY]),

    DEMAND_DAY_SUMMARY[FORECAST_SUBMISSION_YEAR_MONTH_NUMBER]=FORMAT(EOMONTH(SELECTEDVALUE(DEMAND_DAY_SUMMARY[MYT]),0),"YYYYMM"),

    FILTER(

        ALLEXCEPT('DEMAND_DAY_SUMMARY',DEMAND_DAY_SUMMARY[Therapy Area],DEMAND_DAY_SUMMARY[Market],

        DEMAND_DAY_SUMMARY[Global Code],DEMAND_DAY_SUMMARY[Brand]),

        'DEMAND_DAY_SUMMARY'[Year] = MAX('DEMAND_DAY_SUMMARY'[Year]) &&

        'DEMAND_DAY_SUMMARY'[Month] = MAX('DEMAND_DAY_SUMMARY'[Month]) &&

        'DEMAND_DAY_SUMMARY'[Weekend Date] <= MAX(DEMAND_DAY_SUMMARY[Weekend Date]) &&

     --   'DEMAND_DAY_SUMMARY'[Date]<=_MaxWeekEndDate &&

        'DEMAND_DAY_SUMMARY'[Date] <= UTCTODAY()-1

    )

   )

   RETURN IF(MAX(DEMAND_DAY_SUMMARY[Date])>=UTCTODAY()-1,0,Total)

Please help

[Anonymous]

Hi @chlimaye 

What's your expected outcome, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@chlimaye Try using

VAR _MaxWeekEndDate = MAX(DEMAND_DAY_SUMMARY[Weekend Date])
VAR Total =
CALCULATE(
SUM('DEMAND_DAY_SUMMARY'[BASE_ACTUAL_DELIVERED_QUANTITY]),
DEMAND_DAY_SUMMARY[FORECAST_SUBMISSION_YEAR_MONTH_NUMBER] = FORMAT(EOMONTH(SELECTEDVALUE(DEMAND_DAY_SUMMARY[MYT]), 0), "YYYYMM"),
FILTER(
ALLEXCEPT('DEMAND_DAY_SUMMARY', DEMAND_DAY_SUMMARY[Therapy Area], DEMAND_DAY_SUMMARY[Market], DEMAND_DAY_SUMMARY[Global Code], DEMAND_DAY_SUMMARY[Brand]),
'DEMAND_DAY_SUMMARY'[Year] = MAX('DEMAND_DAY_SUMMARY'[Year]) &&
'DEMAND_DAY_SUMMARY'[Month] = MAX('DEMAND_DAY_SUMMARY'[Month]) &&
'DEMAND_DAY_SUMMARY'[Weekend Date] <= _MaxWeekEndDate &&
'DEMAND_DAY_SUMMARY'[Date] <= UTCTODAY() - 1
)
)
RETURN IF(MAX(DEMAND_DAY_SUMMARY[Date]) >= UTCTODAY() - 1, 0, Total)

[chlimaye]

can you please let me know , what did you change?

[chlimaye]

can you please tell me what changes you have done?