Dynamic Text Box based on relative date filter

[Anonymous]

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: The current week displayed is from: 3/22/2020-3/28/2020. 

[Greg_Deckler]

You should be able to do something like:

Measure = "The current week displayed is from: " & MIN('Date'[Date]) & "-" & MAX('Date'[Date])

[Anonymous]

@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. 

[Anonymous · Accepted solution]

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]

@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!

[amitchandak]

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

[Greg_Deckler]

@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.