topic Re: Weekly Commulative some divided over 4 weeks in table in DAX Commands and Tips

Weekly Commulative some divided over 4 weeks in table

chlimaye — Wed, 29 Jan 2025 07:33:33 GMT

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

Re: Weekly Commulative some divided over 4 weeks in table

bhanu_gautam — Wed, 29 Jan 2025 10:39:59 GMT

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)

Re: Weekly Commulative some divided over 4 weeks in table

chlimaye — Wed, 29 Jan 2025 11:19:40 GMT

can you please tell me what changes you have done?

Re: Weekly Commulative some divided over 4 weeks in table

chlimaye — Wed, 29 Jan 2025 11:49:03 GMT

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

Re: Weekly Commulative some divided over 4 weeks in table

Anonymous — Thu, 30 Jan 2025 08:22:04 GMT

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.