Forum Discussion

pborah's avatar
pborah
Icon for Solution Sage rankSolution Sage
4 years ago

Count rows for required date periods given a specific date

HI there,

 

I need to count rows between specific time periods given a specific date. Example -

Context - week begins on a Sunday and ends on a Saturday.

 

Given date - 2022/05/25

 

Count rows from Sunday of the previous week to Saturday, i.e. 2022/05/15 - 2022/05/21

Count rows from Sunday of current week till given date, i.e. 2022/05/22 - 2022/05/25

 

Count rows for last month, i.e. 2022/04/01 - 2022/04/30

Count rows for current month till given date - 2022/05/01-2022/05/25

 

I do have a calendar table separately which has the following columns

 

Thank you.

Need Help
Show and Tell
Tips and Tricks

4 Replies

    • pborah's avatar
      pborah
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Unfortunately I cannot share the pbix since it's confidential govt data.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi pborah ,

     

    Based on your description, a similar date sheet was created as follows:

    M1 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALL ( 'Table' ),
    'Table'[Year] = 2022
        && 'Table'[WeekOfYear] = 21
)
    M2 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALL ( 'Table' ),
    'Table'[Date] <= DATE ( 2022, 05, 25 )
        && 'Table'[Date] >= DATE ( 2022, 05, 22 )
)
    M3 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALL ( 'Table' ),
    'Table'[Year] = 2022
        && 'Table'[Month] = 4
)

     

    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

    • pborah's avatar
      pborah
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Thanks v-henryk-mstf 

       

      This solution does not work for me. The given date is not fixed. Therefore the measures cannot have fixed values for date and week in them. I just used the the given date as an example.