cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AliceW Member
Member

Snapshot for a given date

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.

 

CaseStatusFromUntilDays
A1-New1/1/18 0:001/15/18 0:0015
A2-In Processing1/15/18 0:011/17/18 0:002
B1-New1/7/18 0:001/14/18 0:007
C2-In Processing1/14/18 0:011/20/18 0:006

 

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.

 

Screenshot_1.png

 

 

 

 

 

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Snapshot for a given date

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)
Super User
Super User

Re: Snapshot for a given date

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?

6 REPLIES 6
Super User
Super User

Re: Snapshot for a given date

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 Member
Member

Re: Snapshot for a given date

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

 

For the rest, it's zero. 

 

Super User
Super User

Re: Snapshot for a given date

can you share more accurate data sample then?

Highlighted
AliceW Member
Member

Re: Snapshot for a given date

Hurray!

 

Enhancement NumberLast New ValueChange DateChange Valid Until
8014 - Acknowledged14/09/2018 10:3823/10/2018 0:00
8594 - Acknowledged07/09/2018 14:4223/10/2018 0:00
8604 - Acknowledged07/09/2018 14:2223/10/2018 0:00
8754 - Acknowledged14/09/2018 10:1923/10/2018 0:00
9201 - New03/09/2018 9:3109/10/2018 9:49
9321 - New11/09/2018 13:1919/10/2018 14:42
9761 - New28/09/2018 14:1808/10/2018 7:35
9762 - For Review08/10/2018 7:3509/10/2018 9:54
9841 - New03/10/2018 13:1803/10/2018 13:18
9842 - For Review03/10/2018 13:1819/10/2018 14:05
9851 - New03/10/2018 15:0319/10/2018 14:03
9861 - New03/10/2018 15:1411/10/2018 8:43
52 - For Review18/10/2018 14:4919/10/2018 13:55
161 - New19/10/2018 12:2723/10/2018 0:00
Super User
Super User

Re: Snapshot for a given date

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 Member
Member

Re: Snapshot for a given date

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

 

Thank YOU!!!!