Forum Discussion

jppv20's avatar
jppv20
Solution Sage
3 years ago
Solved

Count records in previous time interval - Direct query mode

Hi,

 

My table visual looks like this (Where "Count id" is a count of the records in that time interval and "Count id -5" should be a measure):

 

 

What I need is the count of ids for the previous interval. Expected output for "Count id -5":

IntervalCount idCount id -5
4/23/2023 12:05:00 AM343251
4/23/2023 12:10:00 AM291343
4/23/2023 12:15:00 AM241291
4/23/2023 12:20:00 AM241241
4/23/2023 12:25:00 AM254241
4/23/2023 12:30:00 AM239254

 

Hope someone can help me out, thanks in advance!

 

Best,

  • Alf94's avatar
    Alf94
    3 years ago

    Hi jppv20,

     

    Can you try the following?

     

    Count ID - 5 = 
    CALCULATE(
        [Count Id],
        OFFSET(
            -1,
            ,
            ORDERBY( Table[Interval], ASC )
        )
    )

     

    If I answered your question, please mark my post as a solution.

     

    Best,

1 Reply

  • Alf94's avatar
    Alf94
    Solution Supplier
    3 years ago

    Hi jppv20,

     

    Can you try the following?

     

    Count ID - 5 = 
    CALCULATE(
        [Count Id],
        OFFSET(
            -1,
            ,
            ORDERBY( Table[Interval], ASC )
        )
    )

     

    If I answered your question, please mark my post as a solution.

     

    Best,