Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |