Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

HotChilli's avatar
HotChilli
Community Champion
4 years ago

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.