Forum Discussion

matrix_user's avatar
matrix_user
Icon for Helper III rankHelper III
3 years ago

DYNAMIC SLICER - Period

Hello,

 

For more than a week...

 

I have been on a task to create a dynamic slicer that can show  daily, weekly, monthly, quarterly, yearly and financial year periods.

 

I have tried everything I could read here on these community pages as well as you tube videos. I have post here https://community.powerbi.com/t5/DAX-Commands-and-Tips/Slicer-not-categorizing-properly-unable-to-display-daily-yearly/m-p/2894797 but as much as I could apply.. I have struggled.

 

I am now desperate... is there anyone kind enough to provide me with a Power BI sample that has such a slicer.

 

Please contact me here or privately.

 

I could do with a solution.

 

Regards

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    matrix_user , refer i have given quite a few calculations here

    https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

     

     

    Additional version of same formula used in blog

     

    Switch Period =
    var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _max =
    SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD"),
    "YTD",_max1,
    "FYTD",_max1,
    "QTD",_max1,
    "MTD",_max1,
    "This Month",eomonth(_max,0),
    "LMTD",date(Year(_max), month(_max) -1, Day(_max)),
    "Last Month",eomonth(_max,-1),
    "WTD", _max1,
    "Cumm",_max1,
    "Rolling 3", _max1,
    "Rolling 6", _max1,
    "Rolling 12",_max1,
    "Rolling 7 Day",_max1,
    "Yesterday",_max1-1,
    BLANK())

    var _min =
    SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD"),
    "YTD",eomonth(_max,-1*MONTH(_max))+1 , //FY April -March
    "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,mod(Month(_max),3)))+1,
    "MTD",eomonth(_max,-1)+1 ,
    "This Month",eomonth(_max,-1)+1 ,
    "LMTD",eomonth(_max,-1)+1,
    "Last Month",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,
    "Rolling 7 Day", date(Year(_max), month(_max) , Day(_max)-7),
    "Yesterday",_max1-1
    BLANK())
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

     


    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))

  • matrix_user's avatar
    matrix_user
    Icon for Helper III rankHelper III
    3 years ago

    Seriously amitchandak...

    Not helpful at all...all that bunch of cut and paste DAX. Im looking for daily, weekly, monthly, quarterly, yearly and financial year periods. Couldn't find anything relevant in any of your posts. Cheers.

    • matrix_user's avatar
      matrix_user
      Icon for Helper III rankHelper III
      3 years ago

      Hi v-shex-msft

       

      Thank you for those links... where exactly are they talking about date periods? They refer to catogories and I failed to see any date periods. Slice by day, week, month, quarter, year fy year and ytd. 

       

      Can anyone really help?