Count rows that meet these requirements

[a10703]

Hi Guys,

I have the dataset one excell file with schedule update

I have collumn start and time for my filters . But i need one function, i need to count all the rows if they have time range 20:00:00  hours to 22:00:00 in all days excepted satuday, because i need to know too if the day is saturday count all range hour 08:00 to 22:00 hours. It's possible?

My collumn on dataset

[KNP · Accepted solution]

Thanks, that's much easier to work with.

See attached PBIX file.

Let me know if you have any questions.

[KNP]

Yes it's possible.

Can you please paste some actual data (not a screenshot)?

Also, a screenshot of your data model would be useful?

Are you using a date dimension?

[a10703]

Hi Dear Friend,

Thank you for your fast asnwer,

My data in excell is:

Data: format as date and time 

ID	DATA
900	17/03/2022 01:23
901	17/03/2022 07:07
902	17/03/2022 07:44
903	17/03/2022 08:21
904	17/03/2022 08:32
905	18/03/2022 20:11
906	18/03/2022 20:15
907	18/03/2022 20:18
908	18/03/2022 21:11

This helps?

[KNP · Accepted solution]

Thanks, that's much easier to work with.

See attached PBIX file.

Let me know if you have any questions.

[a10703]

Hi KNP

how do i see as formulas? or you can explain me how you did? 

Best regards,

[KNP · Accepted solution]

Click on the 'Transform Data' button and see the individual steps, or see the code below.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZdBLCsAgDATQq5SsBfPRRnMV8f7XaIW0oq4fMzDTGlRECEAaUSIj80XGAj0MoVXUUF14l5RcZJViTC5pF2GXPKT8wmj0he6TspOeVJzKRjQL66A616K9B/T+AA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, DATA = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"ID", Int64.Type}, {"DATA", type datetime}}
  ),
  #"Inserted Day Name" = Table.AddColumn(
    #"Changed Type",
    "Day Name",
    each Date.DayOfWeekName([DATA]),
    type text
  ),
  #"Inserted Time" = Table.AddColumn(
    #"Inserted Day Name",
    "Time",
    each DateTime.Time([DATA]),
    type time
  ),
  #"Added Custom" = Table.AddColumn(
    #"Inserted Time",
    "Custom",
    each
      if [Day Name] = "Saturday" and [Time] >= #time(8, 0, 0) and [Time] <= #time(22, 0, 0) then
        1
      else if [Day Name] <> "Saturday" and [Time] >= #time(20, 0, 0) and [Time] <= #time(22, 0, 0) then
        1
      else
        0
  )
in
  #"Added Custom"

[a10703]

Amazing, thank you for your help Sir. 

Best regards!