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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
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.