Snapshot for a given date

[AliceW]

Hi folks,

This is such an important topic for me, so help a girl out, please.

So, I have, say, 2 support cases, which have gone through different statuses in different dates.

Case	Status	From	Until	Days
A	1-New	1/1/18 0:00	1/15/18 0:00	15
A	2-In Processing	1/15/18 0:01	1/17/18 0:00	2
B	1-New	1/7/18 0:00	1/14/18 0:00	7
C	2-In Processing	1/14/18 0:01	1/20/18 0:00	6

I'd like to see how many Cases where in each Status at a given date (and hour, given the business).

In the example above, on 1/2/18 0:00 we only have one Case: A, in 1-New, and none in 2-Processing.

On 1/17/18 0:00 we have both cases in 2-Processing, and none in 1-New.

Basically, I need the user to filter one dane and the report to give this simple result. 

The problem is, I have no idea how to do that. 

Major thanks in advance, people.

Alice

[Stachu · Accepted solution]

you need separate Calendar table that would have all the dates
if you don't have one you can create it with this sytnax (adjust the dates properly)

Calendar = CALENDAR(DATE(2018,1,1),date(2018,12,31))

this table it should have NO active joins to you data table
then this syntax should work (in the visual use date from Calendar table)

Measure = 
VAR ShownDate = MAX('Calendar'[Date])
VAR RelevantRows = FILTER('Table','Table'[From]<=ShownDate+1 && 'Table'[Until]>=ShownDate)
RETURN
COUNTROWS(RelevantRows)

[Stachu · Accepted solution]

you need separate Calendar table that would have all the dates
if you don't have one you can create it with this sytnax (adjust the dates properly)

Calendar = CALENDAR(DATE(2018,1,1),date(2018,12,31))

this table it should have NO active joins to you data table
then this syntax should work (in the visual use date from Calendar table)

Measure = 
VAR ShownDate = MAX('Calendar'[Date])
VAR RelevantRows = FILTER('Table','Table'[From]<=ShownDate+1 && 'Table'[Until]>=ShownDate)
RETURN
COUNTROWS(RelevantRows)

[AliceW]

It partially works. Namely, it only displays the Cases for which changes were made IN that respective day 😞

For the rest, it's zero. 

[Stachu]

can you share more accurate data sample then?

[AliceW]

Hurray!

Enhancement Number	Last New Value	Change Date	Change Valid Until
801	4 - Acknowledged	14/09/2018 10:38	23/10/2018 0:00
859	4 - Acknowledged	07/09/2018 14:42	23/10/2018 0:00
860	4 - Acknowledged	07/09/2018 14:22	23/10/2018 0:00
875	4 - Acknowledged	14/09/2018 10:19	23/10/2018 0:00
920	1 - New	03/09/2018 9:31	09/10/2018 9:49
932	1 - New	11/09/2018 13:19	19/10/2018 14:42
976	1 - New	28/09/2018 14:18	08/10/2018 7:35
976	2 - For Review	08/10/2018 7:35	09/10/2018 9:54
984	1 - New	03/10/2018 13:18	03/10/2018 13:18
984	2 - For Review	03/10/2018 13:18	19/10/2018 14:05
985	1 - New	03/10/2018 15:03	19/10/2018 14:03
986	1 - New	03/10/2018 15:14	11/10/2018 8:43
5	2 - For Review	18/10/2018 14:49	19/10/2018 13:55
16	1 - New	19/10/2018 12:27	23/10/2018 0:00

[Stachu · Accepted solution]

so this is the sytnax I have with adjusted names:

Measure = 
VAR ShownDate = MAX('Calendar'[Date])
VAR RelevantRows = FILTER('Table','Table'[Change Date]<ShownDate+1 && 'Table'[Change Valid Until]>=ShownDate)
RETURN
COUNTROWS(RelevantRows)

I cannot see what is working wrong here - can you specify which Enhancement Number has the data that shows the issue?

[AliceW]

Now it's working - my bad, I must have changed something in your initial variable. 

Thank YOU!!!!