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

      • 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