Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Problem grouping by days

I have a two tables, one with data everyfive minutes in 1200 locations and a calendar with different grouped dates.    I want to be able to analyse the number of consecutive days with 0 values and ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I suggest you to add a date only column in your data table. It will help us to group the table in calculation.

    Datevalue = DATEVALUE('Table'[Time])

    Try this code.

    Measure = 
VAR _T = 
SUMMARIZE(ALL('Table'),'Table'[Datevalue],"Count",CALCULATE(DISTINCTCOUNT('Table'[Value]),ALLEXCEPT('Table','Table'[Datevalue])),"Max",CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Datevalue])))
RETURN
COUNTAX(FILTER(_T,[Datevalue]<=MAX('Table'[Datevalue])&&[Count] = 1&&[Max] = 0),[Datevalue]) +0

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

lbendlin's avatar
lbendlin
Super User
4 years ago

Your sample data only covers one day, and it specifies a day that doesn't exist.  Please provide better sample data that fully covers your issue.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This is a more complete data. Basically I have a lot of locations and I would like to see how many consecutive days do I have with 0 data for a whole day in all locations

     

    Time Location Value
    01/01/2022 00:00 10.455741
    01/01/2022 06:00 10
    01/01/2022 12:00 10
    01/01/2022 18:00 11
    02/01/2022 00:00 10
    02/01/2022 06:00 10
    02/01/2022 12:00 10
    02/01/2022 18:00 10
    03/01/2022 00:00 10
    03/01/2022 06:00 10
    03/01/2022 12:00 10
    03/01/2022 18:00 10
    01/01/2022 00:00 20.433114
    01/01/2022 06:00 20.778561
    01/01/2022 12:00 20.961754
    01/01/2022 18:00 20.878894
    02/01/2022 00:00 20
    02/01/2022 06:00 20
    02/01/2022 12:00 20
    02/01/2022 18:00 20
    03/01/2022 00:00 20
    03/01/2022 06:00 20
    03/01/2022 12:00 20
    03/01/2022 18:00 20

    The output I am loo king for is a measure with the consecutive 0 values as below:

    Date Measure
    01/01/2022 0
    02/01/2022 1
    03/01/2022 2
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      I suggest you to add a date only column in your data table. It will help us to group the table in calculation.

      Datevalue = DATEVALUE('Table'[Time])

      Try this code.

      Measure = 
VAR _T = 
SUMMARIZE(ALL('Table'),'Table'[Datevalue],"Count",CALCULATE(DISTINCTCOUNT('Table'[Value]),ALLEXCEPT('Table','Table'[Datevalue])),"Max",CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Datevalue])))
RETURN
COUNTAX(FILTER(_T,[Datevalue]<=MAX('Table'[Datevalue])&&[Count] = 1&&[Max] = 0),[Datevalue]) +0

      Result is as below.

       

      Best Regards,
      Rico Zhou

       

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