Forum Discussion
Split data across week
Hi All,
I am struggling with one calculation. I have weekly data mapped to Sundays.
The date contains data of the prior week.
Data-
| Scheme | Start Date | End Date |
| Scheme1 | 5-Jul-20 | 2-Aug-20 |
| Scheme2 | 5-Jul-20 | 19-Jul-20 |
| Scheme3 | 12-Jul-20 | 9-Aug-20 |
If a scheme is running for 2 weeks, for both weeks there should be a count.
Example Output -
| Week Date | Scheme1 | Scheme2 | Scheme3 | Total |
| 5-Jul-20 | 1 | 1 | 2 | |
| 12-Jul-20 | 1 | 1 | 1 | 3 |
| 19-Jul-20 | 1 | 1 | 1 | 3 |
| 26-Jul-20 | 1 | 1 | 2 | |
| 2-Aug-20 | 1 | 1 | 2 | |
| 9-Aug-20 | 1 | 1 |
Please help with how do I calculate so that I can get proper count.
Also note I have some data which has start and end date an year apart.
Hi, Anonymous ;
You could create a date table about every sunday, then create a measure to calculate the count like below:
1.create a date table.
Date = FILTER( ADDCOLUMNS( CALENDAR(DATE(2020,7,1),DATE(2020,9,1)),"weekday",WEEKDAY([Date],2)),[weekday]=7)2.create a measure.
Measure = var _value=CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Scheme]=MAX('Table'[Scheme])&&[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date]))) return IF(ISFILTERED('Table'[Scheme]),_value,CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date]))))then use a matrix and the final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Disconnected date table with a measure like :
MeasureT = VAR _dt = MAX(DatesTable[Date]) RETURN COUNTROWS(FILTER(TableG, TableG[Start Date] <= _dt && TableG[End Date] >= _dt))Put the date field in the rows of a matrix, scheme in the columns and measure in the Values.
You might need to edit the conditions in the matrix to suit (<,<= etc).
Filter the dates table to get the rows you need (Sundays, I think).
Also Scheme 1 and 3 look wrong in the desired table.
Hi, Anonymous ;
You could create a date table about every sunday, then create a measure to calculate the count like below:
1.create a date table.
Date = FILTER( ADDCOLUMNS( CALENDAR(DATE(2020,7,1),DATE(2020,9,1)),"weekday",WEEKDAY([Date],2)),[weekday]=7)2.create a measure.
Measure = var _value=CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Scheme]=MAX('Table'[Scheme])&&[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date]))) return IF(ISFILTERED('Table'[Scheme]),_value,CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date]))))then use a matrix and the final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
