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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

HI SusuYes,

The formula that @amitchandak shared is DAX expressions, please create a calculated column on the data model table side with that formulas.
Regards,

Xiaoxin Sheng

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

I'm trying to predefine the date ranges I want but I am having trouble with that as I need to define specific days in a month e.g. from 15 Feb to -5 June 

I tried using the second DAX expression from amitchandak but I get an error:

Semester 1, 20222 = IF(MONTH([Date]) >= DATE(YEAR(TODAY(), 2, 1) && MONTH([Date]) <= DATE(YEAR(TODAY(), 6, 30), "Semester 1", BLANK())))
 

 

I tried to fix the DAX code and got this

Semester 1, 20222 = IF(MONTH([Date]) >= DATE(YEAR(TODAY()), 2, 1) && MONTH([Date]) <= DATE(YEAR(TODAY()), 6, 30), "Sem1", "Error")
However this just return Error everywhere 

I then tried nested if functions as below: 

Semester 1, 2022 = IF(MONTH([Date]) >= 2 && DAY([Date]) >= 13 && MONTH([Date]) <= 2 && DAY([Date]) <= 20, "Orientation Sem 1, 2022",

                        IF(MONTH([Date]) >= 2 && DAY([Date]) >= 21 && MONTH([Date]) <= 6 && DAY([Date]) <= 22, "Semester 1, 2022", "Error"))

However for this did not work as the logical tests seem to clash with each other. The first If expression functions fine but the second one only picks the days = 22 only. 

I need to add multiple date ranges, not just two. 

 

Any ideas how I can achieve this? 

  • 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