Forum Discussion
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)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?
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)
It partially works. Namely, it only displays the Cases for which changes were made IN that respective day :(
For the rest, it's zero.
can you share more accurate data sample then?
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 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?