Forum Discussion

SusuYes's avatar
SusuYes
Icon for Helper III rankHelper III
4 years ago

Adding predefined date slicers e.g. semester 1

Hello,    I need to add slicers that are based on a predefined date ranges. For example, I need to have a button that when clicked, it shows a certain date range.    Example: Semester 1 is from 0...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

SusuYes , Try this

 

Calendar = Var _1  = Addcolumns( CALENDAR(date(2020,02,01) , date(2023,01,31))
, "Start Year", if(format([date], "MMDD")*1 <= 0131 , date(year([Date])-1, 2,1) , date(year([Date]), 2,1))
, "End Year", if(format([date], "MMDD")*1 <= 0131 , date(year([Date]), 1,31) , date(year([Date])+1, 1,31))
,"Start Month", eomonth([date],-1)+1 // if(day([Date]) <=15, EOMONTH([Date],-2)+16, EOMONTH([Date],-1)+16)
,"End Month", eomonth([date],-0) // if(day([Date]) <=15, EOMONTH([Date],-1)+15, EOMONTH([Date],0)+15)
, "Qtr No", Quotient(datediff(if(format([date], "MMDD")*1 <= 0131 , date(year([Date])-1, 2,1) , date(year([Date]), 2,1)), eomonth([date],-1)+1 , month),3)+1,
 "Half No", Quotient(datediff(if(format([date], "MMDD")*1 <= 0131 , date(year([Date])-1, 2,1) , date(year([Date]), 2,1)), eomonth([date],-1)+1 , month),6)+1

, "MMDD",format([date], "MMDD"))
var _2 = ADDCOLUMNS(_1
, "Qtr Start Date", minx(filter(_1, [Qtr No] =EARLIER([Qtr No]) && [Start Year] =EARLIER([Start Year])  ),[Start Month])
, "Half Start Date", minx(filter(_1, [Half No] =EARLIER([Half No]) && [Start Year] =EARLIER([Start Year])  ),[Start Month])
)
return ADDCOLUMNS(_2, "Helf Year Rank", rankx(_2, [Half Start Date],,ASC,Dense) )

 

 

First few are bit complex and few have comments. That will help you move from any day to start

SusuYes's avatar
SusuYes
Icon for Helper III rankHelper III
4 years ago

Sorry is this a calculated coloumn or a new query? 

 and what fields do I use to set the date ranges I want? 

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

    SusuYes , This is code for a new DAX table with all half-year calculations

    • SusuYes's avatar
      SusuYes
      Icon for Helper III rankHelper III
      4 years ago

      Sorry but I don't understand how does that solve the problem. I still cannot define a range of dates using If statement. 

      I need to the additional coloumn to look at the date then return a string based on the value
      so for example from 21Feb 2022 to 05 June 2022 RETURN Semester 1 

      && from 14 Feb 20222 to 20 Feb 2022 RETURN Orientation Sem 1

      && from 05 June to 11 Nov 2022 RETURN Semester 2 

      and so on 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi SusuYes,

        Account to your description, it sounds like you want to create a dynamic calculated column/table based on filter/slicer effects. If that is the case, current power bi does not support these, calculated column/table and filter are work on different data levels.
        For this scenario, I'd like to suggest you use measure expression instead, it hosts on the same level of filter and they can be dynamic changes based on filter selections. (you can use it as a measure filter on 'visual filter level' to apply filter effects)

        Applying a measure filter in Power BI - SQLBI

        Notice: the data level of power bi.

        Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

        Regards,
        Xiaoxin Sheng