Reply
Member
Posts: 55
Registered: ‎08-04-2018
Accepted Solution

Snapshot for a given date

[ Edited ]

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


Accepted Solutions
Super Contributor
Posts: 749
Registered: ‎06-23-2016

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)

View solution in original post

Super Contributor
Posts: 749
Registered: ‎06-23-2016

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?

View solution in original post


All Replies
Super Contributor
Posts: 749
Registered: ‎06-23-2016

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)
Member
Posts: 55
Registered: ‎08-04-2018

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 Contributor
Posts: 749
Registered: ‎06-23-2016

Re: Snapshot for a given date

can you share more accurate data sample then?

Member
Posts: 55
Registered: ‎08-04-2018

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 Contributor
Posts: 749
Registered: ‎06-23-2016

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?

Highlighted
Member
Posts: 55
Registered: ‎08-04-2018

Re: Snapshot for a given date

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

 

Thank YOU!!!!