Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Text Box based on relative date filter

Hello again,   Im looking to make a dynamic text box indicating what the dates are for the current filter. For example, I have a last calendar week relative data filter. I need the text box to say:...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous,

    According to your description, it sounds like your table date range not completed. If this is a case, I think you need to do some calculation to expand them to full week records.

    Measure =
VAR _min =
    MINX ( VALUES ( 'Table'[Date] ), [Date] )
VAR _max =
    MAXX ( VALUES ( 'Table'[Date] ), [Date] )
VAR _minCal =
    _min - WEEKDAY ( _min, 2 ) + 1
VAR _maxCal =
    _max
        + ( 7 - WEEKDAY ( _max, 2 ) )
RETURN
    _minCal & "-" & _maxCal

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Greg_Deckler 

Thanks for your help. That measure returns: 3/24/2020-3/27/2020

EDIT:

I think I know why this is displaying like this. Although my relative date filters are set for 3-22-2020 to 3-28-2020 the only invoices I have in that period are in between the date range above. I'm wondering how to work around this. 

amitchandak's avatar
amitchandak
Super User
6 years ago

You are selecting Min/max from Calendar table as Suggested by Greg_Deckler or from your data table?

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - Right, as amitchandak says, you should be using the same table and column for your min/max as in your slicer. If you are using the Date column from your fact table, try creating a separate Calendar/Date table using CALENDARAUTO or CALENDAR and then creating a relationship on your Date columns.