Forum Discussion

SusuYes's avatar
SusuYes
Helper 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
Helper III
4 years ago

I tried adding that but I get an error

 any idea what is missing? 

 

I'm using a Date table created using this query: 

 

let

    Today=Date.From(DateTime.LocalNow()),

    FromYear = 2017,

    ToYear=2024,

    StartofFiscalYear=7,

    firstDayofWeek=Day.Monday,

    // configuration end

    FromDate=#date(FromYear,1,1),

    ToDate=#date(ToYear,12,31),

    Source=List.Dates(

        FromDate,

        Duration.Days(ToDate-FromDate)+1,

        #duration(1,0,0,0)

    ),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),

    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),

    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),

    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),

    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),

    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),

    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),

    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),

    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),

    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),

    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date],firstDayofWeek), Int64.Type),

    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),

    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),

    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),

    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),

    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),

    #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),

    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month

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
    Helper III
    4 years ago

    Oopsie! thank you that fixed it 😄 

  • SusuYes's avatar
    SusuYes
    Helper 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
      Super 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
        Helper 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?