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 01 Feb 2021 to 30 June 2021, I want to add a button that will filter to this date range. 

 

I saw this solution on YT:Power BI Financial Dashboard: Select Current Month/QTD/YTD Display 📈📊 - YouTube however he had to rewrite all the measures that he used with the filters which is going to be unpracticale for me. Does anyone have any ideas? 

Need Help

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    SusuYes , if they are predefined, then you can column in date table

    example

    if(month([Date]) >=2 && month([Date]) <=6 , "Semester 1", blank())

     

    or

     

    if(month([Date]) >=date(year(today(),2,1) && month([Date]) <=date(year(today(),6,30)  , "Semester 1", blank())

     

    and use that as filter

    • 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