Forum Discussion
Dynamic Text Box based on relative date filter
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 & "-" & _maxCalRegards,
Xiaoxin Sheng
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.
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
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!