Forum Discussion
Weekly Commulative some divided over 4 weeks in table
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 fin...
bhanu_gautam
Super User
Super Userchlimaye 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)
