Forum Discussion
Resolver IHow to dynamically determine a period for visual
I'm facing a challenge in Power BI, I need to create a way to dynamically return the last 3 months in the visual based on my filter visual.
Basically, I have more than one visual on the same tab where they are filtered by a specific range in the date filter, but there's one visual that I want to treat differently.
Example:
We filter by the range of 01/01/2023 - 31/12/2023.
This visual should only return the range of 01/10/2023 - 31/12/2023.
Current
Expected
Here is the Power Query data source.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZM5bsMwEEXvonqAzL6U9jUMI4X7pEnunxEDSAZMGlCh4uGTf97wdtuQPvpjZNlguzwe379fP5+X/udySA11le0O/yDPQEOEQMMkP0CZgZoEWigSdoA6Ax0FBLPc+QBtBookRLpVnqBP7ygBLG4ZJxjT1qpgWqZZB5jTRFeQxIonsGZgYQIpIdXRmnA6HmZwCpWkA5yaKWeIro50Jk7NRCdaOqadA39JvI6j+47YpfGN6x30AEWN8qi16x0M5b21BvHa9Q62PWBFxHjj+jpaW5uhwPK165Eovbhm/HTFF9WDa3+9kqYYa9Xj5DDA9i3ha9VjOoCEYqrsa9U72BsGwSr2xvTg+iEQo5Xl2vQAwUg9syO3+/0P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Acc = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Acc", type text}, {"Value", type number}})
in
#"Changed Type"
And here is the script for the calendar dimension
dimCalendar =
ADDCOLUMNS (
CALENDARAUTO(),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Year-Month", FORMAT ( [Date], "YYYY-MM" ),
"Month Name", FORMAT ( [Date], "MMMM" ),
"Quarter", QUARTER ( [Date] ),
"Day of Week", WEEKDAY ( [Date] ),
"Day of Month", DAY ( [Date] ),
"Weekday Name", FORMAT ( [Date], "dddd" ),
"Weekday Short", FORMAT ( [Date], "ddd" ),
"Year Week", YEAR ( [Date] ) & "-" & WEEKNUM ( [Date], 2 ),
"Month Year", FORMAT ( [Date], "MM/YYYY" ),
"Quarter Year", "Q" & FORMAT ( [Date], "Q" ) & "-" & YEAR ( [Date] )
)
The measure I created for illustration.
Revenue = SUM(factBase[Value])
You're welcome! I appreciate your attention.
Gabriel_Pedri , if you want less values than selection , you can have measure like
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = eomonth(_max, -3) +1
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))But if you want more than the selected range of value, the slicer needs to be on an independent/disconnected date
//Date1 is an independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
2 Replies
- amitchandak
Super User
Gabriel_Pedri , if you want less values than selection , you can have measure like
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = eomonth(_max, -3) +1
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))But if you want more than the selected range of value, the slicer needs to be on an independent/disconnected date
//Date1 is an independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hi, Gabriel_Pedri
Based on your description, you can refer to amitchandak reply to solve this problem. If it can't be solved, you can refer to the following methods
Method 1:Column = VAR _maxdate = MAX ( 'Table'[Date] ) VAR _last_three_month = EOMONTH ( _maxdate, -3 ) + 1 RETURN IF ( [Date] >= _last_three_month, "Last three month", FORMAT ( 'Table'[Date], "yyyy/mm" ) )Method 2:
Use Preselected Slicer, this visual object sets the default selection for the slicer. You can refer to the blog link below for specific information.
Related link: How to dynamically select the default values we ne... - Microsoft Fabric CommunityBest Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
