Default contents of table to latest date

DAX DAX Calculations
1

Hi,

Please see my sample pbix: https://www.dropbox.com/s/cgi72ptrirubkvk/Default%20table%20to%20latest%20items.pbix?dl=0

I have a simple [Reservations] table, containing items reserved on a given Reserve Date. My report needs to:

  1. Offer a bar chart showing the number of items on reserve over time - DONE
  2. Offer a Card showing the number of items on Reserve, defaulting to the current date (max date in the table) or if a date is selected from the bar chart, to show the number on that day - DONE
  3. A table listing the items on order, defaulting to the current day or showing the items on order if another date is selected from the bar chart - NOT DONE.

I have created the following measure:

# Items On Reserve =
VAR __maxDate =
    MAX ( Reservations[Reserve Date] )
RETURN
    CALCULATE ( COUNTROWS ( Reservations ), Reservations[Reserve Date] = __maxDate )

Which allows my card to default to the number of items on reserve today - eg 01/09/2019 it reports 4 items and if I select a date fro the bar chart, my card reports the correct number of items for that day.
The table also reflects shows these items.

However if I don’t select a date from the bar chart, my table shows all items in the table. Is there a way to default the table to show the items for today (4 rows) if the user does nothing and to still show the items relating to a historical date if the user selects a date from the bar chart? I was hoping that he presence of the measure in the table would do this, but alas not

Many thanks!

Mark

2

I think I have found a solution, by filtering the table visual on the measure below:

_Filter Reservations = 
// Internal measure to control which records are displayed in the Reservations table, by filtering the visual on this measure
VAR __maxDate =
    CALCULATE ( MAX ( Reservations[Reserve Date] ), ALL ( Reservations ) )
VAR __SelectedDate =
    SELECTEDVALUE ( Dates[Date])
VAR __DefaultToToday =
    IF ( __SelectedDate = __maxDate, "Y", "N" )
VAR __FilterToSpecificDate =
    IF ( ISFILTERED ( Reservations[Reserve Date] ), "Y", "N" )
RETURN
    IF ( __DefaultToToday = "Y" || __FilterToSpecificDate = "Y", "Y", "N" )

The idea is that the measure returns a ‘Y’ if the date in the table matches that of the max date (current day) or if Reserve Date is filtered.

See solution here: https://www.dropbox.com/s/glv79rdv3ccltax/Default%20table%20to%20latest%20items%202.pbix?dl=0

Thoughts appreciated if this DAX could be improved or if there is a better solution which I have not considered!

Thanks

Mark

3

@Mark,

Hey, if it works, it works… But here is an alternative construct using measure branching that I think is a little simpler and easier to understand.

MaxDate = 
SWITCH( ISFILTERED( Dates[Date] ),
    TRUE, SELECTEDVALUE( Dates[Date] ),
    FALSE, TODAY()
)

Simplified # Items On Reserve = 
VAR TempDate=[MaxDate]

RETURN
    COUNTROWS(
     FILTER(Reservations, RELATED(Dates[Date]) = TempDate)
    )

Full solution file posted below. Hope this is helpful.

  • Brian

eDNA - Default table to latest items solution2.pbix (104.6 KB)