Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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!
You are selecting Min/max from Calendar table as Suggested by @Greg_Deckler or from your data table?
@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.
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |