Adding predefined date slicers e.g. semester 1

[SusuYes]

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? 

[amitchandak]

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

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]

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]

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]

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

Sorry is this a calculated coloumn or a new query? 

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

[amitchandak]

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

[SusuYes]

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]

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

[SusuYes]

Oopsie! thank you that fixed it 😄