Forum Discussion
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.
| 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
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)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
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)
