Forum Discussion

chlimaye's avatar
chlimaye
Regular Visitor
1 year ago

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 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

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      chlimaye
      Regular Visitor
      1 year ago

      can you please tell me what changes you have done?

    • chlimaye's avatar
      chlimaye
      Regular Visitor
      1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.