Dax Calc.- Values for previous time range

[EmaVasileva]

Hi all,

I need some help with two DAX dynamic calculations which I can use in a Card visualization/or as a visual filter. I have the following data:

Team A works between 12:00:01 AM until 08:00:00AM
Team B works between 08:00:01 AM until 04:00:00 PM
Team C works between 04:00:01 PM until 12:00:00 AM
And there is more than one row for every time range.

If current time is 12:05:00 PM (and Team B is working) I need to show the sum of all values for the previous team (between 12 AM and 07AM- Team A), If the current time is 4:30 PM (and Team C is working now) I need to show the sum of values for previous team B (all values between 07Am and 4PM). And if the current time is  12:01:00AM (and Team A is working) I need to show the sum of all values for previous day-team (Team C-last values).

Also, I need a second calculation. If current time is 12:05:00PM (and Team B is working) I need to show the sum of all values for 2 teams before the current (between 4Pm-12Am Team C (previous day).

Available data:

Date dd/mm/yyyy	Time hh:mm:ss	Team (based on the time on the left)	Value	Time Now
09/08/2021	12:00:01 AM	A	5	Now()
09/08/2021	01:13:00 AM	A	10	Now()
09/08/2021	09:17:05 AM	B	5	Now()
09/08/2021	03:03:00 PM	B	5	Now()
09/08/2021	05:19:13 PM	C	15	Now()
09/08/2021	11:15:00 PM	C	5	Now()
10/08/2021	00:10:06 AM	A	10	Now()
10/08/2021	10:02:00 AM	B	5	Now()
10/08/2021	11:09:00 AM	B	5	Now()

Needed new columns:

Previous team values	Previous team values - 1

Thank you.

[V-pazhen-msft]

@EmaVasileva 

I see the logic but a bit confusing with your requirement, can you also add your expected result column into the above table, this should be clear.

Paul Zheng _ Community Support Team
Best Regards

[EmaVasileva]

Hi @V-pazhen-msft ,

Current data:

Team A works between 12:00:01 AM until 08:00:00AM

Team B works between 08:00:01 AM until 04:00:00 PM

Team C works between 04:00:01 PM until 12:00:00 AM

Date dd/mm/yyyy	Time	Team	Value
11/08/2021	12:05:01 AM	A	5
11/08/2021	1:13:00 AM	A	10
11/08/2021	9:17:05 AM	B	5
11/08/2021	3:03:00 PM	B	20
11/08/2021	5:19:13 PM	C	5
11/08/2021	11:15:00 PM	C	15
12/08/2021	12:10:06 AM	A	30
12/08/2021	10:02:00 AM	B	25
12/08/2021	11:09:00 AM	B	10

11.08.2021 Sum of values Team A = 5+10 = 15
11.08.2021 Sum of values Team B= 5+20 = 25
11.08.2021 Sum of values Team C = 5+15 = 20
12.08.2021 Sum of values Team A = 5+10 = 15
12.08.2021 Sum of values Team B = 25+10 = 35

Desired Result:

If current time is: (using Now() formula)	Current date (Today())	New calculation: Team	New calculation: Value
Between 04:00:01 PM until 12:00:00 AM	12.08.2021	B	35
Between 08:00:01 AM until 04:00:00 PM	12.08.2021	A	30
Between 12:00:01 AM until 08:00:00AM	12.08.2021	C	20

If current time is 4:40 PM (Team C) and the date in the first column is equal to Today’s date, return the sum of values for range between 08:00:01 AM until 04:00:00 PM (Team B) = 35

If current time is 1:00:00 PM (Team B) and the date in the first column = Today’s date, return the sum of values for range between 12:00:01 AM until 08:00:00AM (Team A) = 30

If current time is 7:30:00 AM (Team A) and the date in the first column = Today’s date, return the sum of values for range between 04:00:01 PM until 12:00:00 AM (team C) for YESTERDAY = 20

*I need Dax calculation, because I can't use Advanced Query.

Thank you.

[Greg_Deckler · Accepted solution]

@EmaVasileva Seems like it should be something along the lines of:

Measure =
  VAR __Now = NOW()
  VAR __Today = TODAY()
  VAR __Team =
    SWITCH(TRUE(),
      __Now >= TIME(8,0,0) && __Now <=TIME(12,0,1),"A",
      __Now >= TIME(8,0,1) && __Now <=TIME(16,0,0),"B",
      __Now >= TIME(16,0,1) && __Now <=TIME(23,59,59),"C"
    )
RETURN
  SWITCH(TRUE(),
    __Team = "B",SUMX(FILTER('Table',[Date]=__Today && [Time] >= TIME(8,0,0) && [Time] <=TIME(12,0,1),
    __Team = "C",SUMX(FILTER('Table',[Date]=__Today && [Time] >= TIME(8,0,1) && [Time] <=TIME(16,0,0),
    SUMX(FILTER('Table',[Date]=__Today-1 && [Time] >= TIME(16,0,1) && [Time] <=TIME(23,59,59)
  )