Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AliceW
Impactful Individual
Impactful Individual

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
Stachu
Community Champion
Community Champion

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)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

Stachu
Community Champion
Community Champion

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

6 REPLIES 6
Stachu
Community Champion
Community Champion

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)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

AliceW
Impactful Individual
Impactful Individual

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
Community Champion
Community Champion

can you share more accurate data sample then?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

AliceW
Impactful Individual
Impactful Individual

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
Stachu
Community Champion
Community Champion

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

AliceW
Impactful Individual
Impactful Individual

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

 

Thank YOU!!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.