Forum Discussion

AliceW's avatar
AliceW
Icon for Power Participant rankPower Participant
7 years ago
Solved

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 Sta...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    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)
  • Stachu's avatar
    Stachu
    7 years ago

    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?

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

can you share more accurate data sample then?

AliceW's avatar
AliceW
Icon for Power Participant rankPower Participant
7 years ago

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's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      AliceW
      Icon for Power Participant rankPower Participant
      7 years ago

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

       

      Thank YOU!!!!