Forum Discussion
Issue with Calculating Cumulative Sales for Same Weekday Last Year
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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
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 | |||
| Date | Day | SameWeekday Last Year's Date | Last Year's Day |
| 3-Jan-20 | Fri | 4-Jan-19 | Fri |
| 10-Jan-20 | Fri | 11-Jan-19 | Fri |
| 17-Jan-20 | Fri | 18-Jan-19 | Fri |
| 24-Jan-20 | Fri | 25-Jan-19 | Fri |
| 31-Jan-20 | Fri | 1-Feb-19 | Fri |
| 1-Jan-21 | Fri | 3-Jan-20 | Fri |
| 8-Jan-21 | Fri | 10-Jan-20 | Fri |
| Calender Table | |||
| Date, | Year_yyyy, | Month_Value, | Day_Value, |
| 1-May-17 | 2017 | 5 | 1 |
| 2-May-17 | 2017 | 5 | 2 |
| 3-May-17 | 2017 | 5 | 3 |
| 4-May-17 | 2017 | 5 | 4 |
| 5-May-17 | 2017 | 5 | 5 |
| 6-May-17 | 2017 | 5 | 6 |
| 7-May-17 | 2017 | 5 | 7 |
| GGCA_Purchase_data | |||
| ReceptionDate | RegisterPrice | RegisterPriceTaxIn | |
| 1-May-17 | 1000 | 1100 | |
| 2-May-17 | 1500 | 1650 | |
| 3-May-17 | 2000 | 2200 | |
| 4-May-17 | 1000 | 1100 | |
| 5-May-17 | 1200 | 1320 |
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.