MAT, MAT_LY, YTD, YTD_LY, Q, Q_LY

[Syndicate_Admin]

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

[v-yiruan-msft · Accepted 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

[amitchandak]

@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

[Syndicate_Admin]

Thank you very much for if time, I hope you can adapt it to the calendar table I already have.

[v-yiruan-msft · Accepted 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 · Accepted solution]

Thank you very much, it's exactly what I needed.

Best regards