Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear I have a problem I hope you can help me:
I have a calendar table (dynamic), which I will use, but I need to create, an auxiliary table where I can choose the Type of Grouping and this one selects me only the dates I chose, for example if I choose YTD_LY, choose the dates between January and today, but from the previous year.
I attach the pbix file, with my calendar table, I hope you can help me
https://drive.google.com/file/d/1y66j1o2rNKqJYrtnoxxSFSbAK7WOAkwX/view?usp=sharing
Solved! Go to Solution.
Hi @jminanoc ,
I updated your sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create period dimension table
2. Create a measure as below and apply a visual-level filter on the visual with the condition(Measure is 1😞
Measure =
VAR _selperiod =
SELECTEDVALUE ( 'Periods'[Period] )
VAR _seldate =
SELECTEDVALUE ( 'Calendario'[Fecha] )
VAR _stdate =
TODAY ()
VAR _curyear =
YEAR ( _stdate )
VAR _lyeomonth =
EOMONTH ( DATE ( _curyear - 1, MONTH ( _stdate ), 1 ), 0 )
VAR _lycurdate =
IF (
DAY ( TODAY () ) > DAY ( _lyeomonth ),
_lyeomonth,
DATE ( _curyear - 1, MONTH ( _stdate ), DAY ( _stdate ) )
)
VAR _mat =
CALCULATETABLE (
VALUES ( 'Calendario'[Fecha] ),
FILTER (
'Calendario',
DATEDIFF ( _seldate, TODAY (), MONTH ) >= 0
&& DATEDIFF ( _seldate, _stdate, MONTH ) <= 12
)
)
VAR _lymat =
CALCULATETABLE (
VALUES ( 'Calendario'[Fecha] ),
FILTER (
'Calendario',
DATEDIFF ( _seldate, _lycurdate, MONTH ) >= 0
&& DATEDIFF ( _seldate, _lycurdate, MONTH ) <= 12
)
)
VAR _ytd =
DATESBETWEEN ( 'Calendario'[Fecha], DATE ( _curyear, 1, 1 ), _stdate )
VAR _lytd =
DATESBETWEEN ( 'Calendario'[Fecha], DATE ( _curyear - 1, 1, 1 ), _lycurdate )
VAR _q =
CALCULATETABLE (
VALUES ( 'Calendario'[Fecha] ),
FILTER (
'Calendario',
'Calendario'[Año] = _curyear
&& 'Calendario'[Quarter] = CONCATENATE ( "Q", QUARTER ( _stdate ) )
)
)
VAR _lyq =
CALCULATETABLE (
VALUES ( 'Calendario'[Fecha] ),
FILTER (
'Calendario',
'Calendario'[Año] = _curyear - 1
&& 'Calendario'[Quarter] = CONCATENATE ( "Q", QUARTER ( _stdate ) )
)
)
RETURN
SWITCH (
_selperiod,
"MAT", IF ( _seldate IN _mat, 1, 0 ),
"MAT_LY", IF ( _seldate IN _lymat, 1, 0 ),
"Q", IF ( _seldate IN _q, 1, 0 ),
"Q_LY", IF ( _seldate IN _lyq, 1, 0 ),
"YTD", IF ( _seldate IN _ytd, 1, 0 ),
"YTD_LY", IF ( _seldate IN _lytd, 1, 0 )
)
If the above one can't help you get the desired result, please provide more details on your expected result with backend logic and special examples. It is better if you can share a simplified pbix file with these information. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
@Syndicate_Admin , Try like this example
Switch Period =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min =
SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD"),
"YTD",eomonth(_max,-1*MONTH(_max))+1 , //FY April -March
"LYTD",eomonth(_max,-1*MONTH(_max))+1 ,
"FYTD",if( Month(_max) <4 , date(year(_max)-1,4,1) ,date(year(_max),4,1)), //FY April -March
"QTD",eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1,
"MTD",eomonth(_max,-1)+1 ,
"LMTD",eomonth(_max,-1)+1 ,
"LYMTD",eomonth(_max,-1)+1 ,
"WTD", _max -WEEKDAY(_max,2)+1,
"Cumm", Minx(ALLSELECTED('Date'),'Date'[Date]),
"Rolling 3", date(Year(_max), month(_max) -3, Day(_max))+1,
"Rolling 6", date(Year(_max), month(_max) -6, Day(_max))+1,
"Rolling 12", date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
var _max1 = SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD") ,
"LYTD",Date(Year(_max)-1, month(_max), Day(_max))
"LMTD",Date(Year(_max), month(_max)-1, Day(_max))
"LYMTD",Date(Year(_max)-1, month(_max), Day(_max) ) ,
_max)
var _min1 = SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD") ,
"LYTD",Date(Year(_min)-1, month(_min), Day(_min))
"LMTD",Date(Year(_min), month(_min)-1, Day(_min))
"LYMTD",Date(Year(_min)-1, month(_min), Day(_min) ) ,
_min)
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min1,_max1))
Correct the values as per need
Thank you very much for if time, I hope you can adapt it to the calendar table I already have.
Hi @jminanoc ,
I updated your sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create period dimension table
2. Create a measure as below and apply a visual-level filter on the visual with the condition(Measure is 1😞
Measure =
VAR _selperiod =
SELECTEDVALUE ( 'Periods'[Period] )
VAR _seldate =
SELECTEDVALUE ( 'Calendario'[Fecha] )
VAR _stdate =
TODAY ()
VAR _curyear =
YEAR ( _stdate )
VAR _lyeomonth =
EOMONTH ( DATE ( _curyear - 1, MONTH ( _stdate ), 1 ), 0 )
VAR _lycurdate =
IF (
DAY ( TODAY () ) > DAY ( _lyeomonth ),
_lyeomonth,
DATE ( _curyear - 1, MONTH ( _stdate ), DAY ( _stdate ) )
)
VAR _mat =
CALCULATETABLE (
VALUES ( 'Calendario'[Fecha] ),
FILTER (
'Calendario',
DATEDIFF ( _seldate, TODAY (), MONTH ) >= 0
&& DATEDIFF ( _seldate, _stdate, MONTH ) <= 12
)
)
VAR _lymat =
CALCULATETABLE (
VALUES ( 'Calendario'[Fecha] ),
FILTER (
'Calendario',
DATEDIFF ( _seldate, _lycurdate, MONTH ) >= 0
&& DATEDIFF ( _seldate, _lycurdate, MONTH ) <= 12
)
)
VAR _ytd =
DATESBETWEEN ( 'Calendario'[Fecha], DATE ( _curyear, 1, 1 ), _stdate )
VAR _lytd =
DATESBETWEEN ( 'Calendario'[Fecha], DATE ( _curyear - 1, 1, 1 ), _lycurdate )
VAR _q =
CALCULATETABLE (
VALUES ( 'Calendario'[Fecha] ),
FILTER (
'Calendario',
'Calendario'[Año] = _curyear
&& 'Calendario'[Quarter] = CONCATENATE ( "Q", QUARTER ( _stdate ) )
)
)
VAR _lyq =
CALCULATETABLE (
VALUES ( 'Calendario'[Fecha] ),
FILTER (
'Calendario',
'Calendario'[Año] = _curyear - 1
&& 'Calendario'[Quarter] = CONCATENATE ( "Q", QUARTER ( _stdate ) )
)
)
RETURN
SWITCH (
_selperiod,
"MAT", IF ( _seldate IN _mat, 1, 0 ),
"MAT_LY", IF ( _seldate IN _lymat, 1, 0 ),
"Q", IF ( _seldate IN _q, 1, 0 ),
"Q_LY", IF ( _seldate IN _lyq, 1, 0 ),
"YTD", IF ( _seldate IN _ytd, 1, 0 ),
"YTD_LY", IF ( _seldate IN _lytd, 1, 0 )
)
If the above one can't help you get the desired result, please provide more details on your expected result with backend logic and special examples. It is better if you can share a simplified pbix file with these information. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Thank you very much, it's exactly what I needed.
Best regards