Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello All ,
I need some help with Season Slicer.
I have Season and Date (Weekly Snapshots)table.
SEASON |
M19 |
S19 |
M20 |
S20 |
M21 |
S21 |
M22 |
S22 |
Season range :
If it is M Jan -July
If it is S April -March
If user selects M22, then the dates should be Jan thru July of same year(2022).
If user selects S22 then the dates shuld be Appril 2021 thru March 2022.
Can some one help with this please.
TIA
Solved! Go to Solution.
Hi @Puja ,
You can modify it to the following dax:
When M is selected, the minimum value from January to July of the Season Year is displayed.
When S is selected, the March of Season Year -1 to the April minimum of Season Year are displayed.
Flag =
VAR _select =
SELECTEDVALUE ( 'Season Table'[Season] )
VAR _year =
VALUE ( "20" & "" & RIGHT ( _select, 2 ) )
VAR _code =
MAXX (
FILTER ( ALL ( 'Season Table' ), 'Season Table'[Season] = _select ),
[Code]
)
VAR _MJulyfirst =
MINX (
FILTER (
ALL ( 'Date table' ),
YEAR ( 'Date table'[Date] ) = _year
&& MONTH ( 'Date table'[Date] ) = 7
),
[Date]
)
VAR _SMarchfirst =
MINX (
FILTER (
ALL ( 'Date table' ),
YEAR ( 'Date table'[Date] ) = _year
&& MONTH ( 'Date table'[Date] ) = 3
),
[Date]
)
VAR _Smindate =
DATE ( _year - 1, 4, 1 )
RETURN
SWITCH (
TRUE (),
_code = "M"
&& MAX ( 'Date table'[Date] ) >= DATE ( _year, 1, 1 )
&& MAX ( 'Date table'[Date] ) <= _MJulyfirst, 1,
_code = "S"
&& MAX ( 'Date table'[Date] ) >= _Smindate
&& MAX ( 'Date table'[Date] ) <= _SMarchfirst, 1,
0
)
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Puja ,
You can modify it to the following dax:
When M is selected, the minimum value from January to July of the Season Year is displayed.
When S is selected, the March of Season Year -1 to the April minimum of Season Year are displayed.
Flag =
VAR _select =
SELECTEDVALUE ( 'Season Table'[Season] )
VAR _year =
VALUE ( "20" & "" & RIGHT ( _select, 2 ) )
VAR _code =
MAXX (
FILTER ( ALL ( 'Season Table' ), 'Season Table'[Season] = _select ),
[Code]
)
VAR _MJulyfirst =
MINX (
FILTER (
ALL ( 'Date table' ),
YEAR ( 'Date table'[Date] ) = _year
&& MONTH ( 'Date table'[Date] ) = 7
),
[Date]
)
VAR _SMarchfirst =
MINX (
FILTER (
ALL ( 'Date table' ),
YEAR ( 'Date table'[Date] ) = _year
&& MONTH ( 'Date table'[Date] ) = 3
),
[Date]
)
VAR _Smindate =
DATE ( _year - 1, 4, 1 )
RETURN
SWITCH (
TRUE (),
_code = "M"
&& MAX ( 'Date table'[Date] ) >= DATE ( _year, 1, 1 )
&& MAX ( 'Date table'[Date] ) <= _MJulyfirst, 1,
_code = "S"
&& MAX ( 'Date table'[Date] ) >= _Smindate
&& MAX ( 'Date table'[Date] ) <= _SMarchfirst, 1,
0
)
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @v-yangliu-msft ,
I just noticed one limitation,
When I select S23, should show , 2022 April until 2023 March.
But I see blanks. Any thoughts ?
Hi @Puja ,
Here are the steps you can follow:
1. Create measure.
Flag =
var _select=SELECTEDVALUE('Season Table'[Season])
var _code=MAXX(FILTER(ALL('Season Table'),'Season Table'[Season]=_select),[Code])
var _MJulyfirst=MINX(FILTER(ALL('Date table'),YEAR('Date table'[Date])=2022&&MONTH('Date table'[Date])=7),[Date])
var _SMarchfirst=MINX(FILTER(ALL('Date table'),YEAR('Date table'[Date])=2022&&MONTH('Date table'[Date])=3),[Date])
return
SWITCH(
TRUE(),
_code="M"&&MAX('Date table'[Date])>=DATE(2022,1,1)&&MAX('Date table'[Date])<=_MJulyfirst,1,
_code="S"&&MAX('Date table'[Date])>=DATE(2021,4,1)&&MAX('Date table'[Date])<=_SMarchfirst,1,0)
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
M22:
S22:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @v-yangliu-msft Thank you somuch.
This looks promising solution.
Can we make it dynamic for other seasons as well ?
Date range is same for M and S types.
If user selects M type, then the dates should be Jan thru July (min date) of same year.
If user selects S type then the dates shuld be Appril thru March(min date) .
Note: season number represents year.
ex: M21 = YEAR 2021
S13 = YEAR 2013
Thanks again.
Puja
Can you share sample data and sample output in table format?
@Puja , You have map these season with you date table. Date table should have column for that range.
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |