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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago
You should be able to do something like:

Measure = "The current week displayed is from: " & MIN('Date'[Date]) & "-" & MAX('Date'[Date])
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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      Anonymous 

      Your solution works for my specific requirement when the +1 is deleted and (7- WEEKDAY(_max,2) is changed to (7- WEEKDAY (_max,1). Thanks!