Forum Discussion

RYOH_0130's avatar
RYOH_0130
Regular Visitor
2 years ago
Solved

Issue with Calculating Cumulative Sales for Same Weekday Last Year

Hello Power BI Community, I am encountering an issue with calculating the cumulative sales for the same weekday last year. Here are the key details of the problem: Same Weekday Last Year: I am ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi RYOH_0130 ,

     

    I did simple samples, mainly for the same weekday screenings last year for the study and you can check the results below:

     

    LastYearSameWeekDay = var _t = ADDCOLUMNS('Table',"a",SUMX(FILTER(ALL('Table'),YEAR([Date])=YEAR(EARLIER([Date]))-1&&MONTH([Date])=MONTH(EARLIER([Date]))&&DAY([Date])>=DAY(EARLIER([Date]))-7&&WEEKDAY([Date])=WEEKDAY(EARLIER([Date]))),[Value]))
RETURN SUMX(_t,[a])

     

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi RYOH_0130 ,

 

Not sure about your date table, please provide sample data that maps your data model

 

Best regards,
Community Support Team_ Scott Chang

  • RYOH_0130's avatar
    RYOH_0130
    Regular Visitor
    2 years ago

    Dear Anonymous 

    Thank you for your response.
    Please find below the sample data for the data map for your review.

    Best regads,
    Ryo

    Previous Data Table
    DateDaySameWeekday
    Last Year's Date    		Last Year's Day
    3-Jan-20Fri4-Jan-19Fri
    10-Jan-20Fri11-Jan-19Fri
    17-Jan-20Fri18-Jan-19Fri
    24-Jan-20Fri25-Jan-19Fri
    31-Jan-20Fri1-Feb-19Fri
    1-Jan-21Fri3-Jan-20Fri
    8-Jan-21Fri10-Jan-20Fri
        
    Calender Table
    Date,Year_yyyy,Month_Value,Day_Value,
    1-May-17201751
    2-May-17201752
    3-May-17201753
    4-May-17201754
    5-May-17201755
    6-May-17201756
    7-May-17201757
        
    GGCA_Purchase_data 
    ReceptionDateRegisterPriceRegisterPriceTaxIn 
    1-May-1710001100 
    2-May-1715001650 
    3-May-1720002200 
    4-May-1710001100 
    5-May-1712001320 
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi RYOH_0130 ,

       

      I did simple samples, mainly for the same weekday screenings last year for the study and you can check the results below:

       

      LastYearSameWeekDay = var _t = ADDCOLUMNS('Table',"a",SUMX(FILTER(ALL('Table'),YEAR([Date])=YEAR(EARLIER([Date]))-1&&MONTH([Date])=MONTH(EARLIER([Date]))&&DAY([Date])>=DAY(EARLIER([Date]))-7&&WEEKDAY([Date])=WEEKDAY(EARLIER([Date]))),[Value]))
RETURN SUMX(_t,[a])

       

       

      An attachment for your reference. Hope it helps!

       

      Best regards,
      Community Support Team_ Scott Chang

       

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