Forum Discussion

Noelle's avatar
Noelle
Helper I
5 years ago

Count rows at different time zone

Hello,

 

I would like to count rows on a production sheet, every day. I need to count rows from 6am to 6 am, and show it on a graph with usual date 12pm to 12pm.

I have on the same table a column with "real date", "date -6 hours", and several parameters not relevant here.

 

As an example, here below would be my data :

start date/timestart date/time -6hop
12.07.2021 09:0012.07.2021 03:001
12.07.2021 15:0012.07.2021 09:002
12.07.2021 17:0012.07.2021 11:002
12.07.2021 19:0012.01.2021 13:004
13.07.2021 01:0012.07.2021 19:005
13.07.2021 05:0012.07.2021 23:006
13.07.2021 07:0013.07.2021 01:007
13.07.2021 15:0013.07.2021 09:008

 

And here below the result I wish I could calculate (and show it on a graph)

start date/timeNumber of op
12.07.20216
13.07.20212

 

But all I can manage to have is :

start date/timeNumber of op
12.07.20214
13.07.20214

 

I though that I could count rows for "start date/time -6h.[date]" and relate it to "start date/time" at some point...

 

I have tried to create two tables, one for "start date/time" (actual date/time) and one for "start date/time -6h" (the date/time I want to rely on to calculate). But I cannot get the correct formula/relationship to have what I'm looking for.

 

Any thought?

Thank you for your help!!
Noelle

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Noelle 

     

    I have done one like this, you can use two tables as well

    Relationships

    Number of op = COUNTROWS(factTable)

Number of op -6h = CALCULATE(COUNTROWS(factTable),USERELATIONSHIP(dimTable[start date/time],factTable[Date - 6h]))

     

     

  • Noelle's avatar
    Noelle
    Helper I
    5 years ago

    Thank you Anonymous . I must do something wrong, since I keep getting the same result 😞

     

    Relationships :

    Main : 'DATE DE PRODUCTION'[Date]->'Feuil1'[Date]

    Second : 'DATE DE PRODUCTION'[Date]->'Feuil1'[Date -6]

     

    Nbre de Rx = COUNTROWS('Feuil1')
    Nbre de Rx -6 = CALCULATE(COUNTROWS('Feuil1'),USERELATIONSHIP('DATE DE PRODUCTION'[Date],Feuil1[Date -6]))

     

    Can you see what's my mistake?

    Thanks

    Noelle

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Noelle 

       

      I have no idea...it looks like the same, but it works in my sample file...I can send you the .pbix file if you need, you can pm your email address