Forum Discussion

PowerBiTech's avatar
PowerBiTech
Frequent Visitor
2 years ago

Create date range slicer with custom start date and end date

Hi All 🙂 

I have one table name called "Content" and around 10 columns are there and we have to filter out with the "ID_date"

 

  1. Want to create a Power BI report with a slicer allowing users to choose between predefined date ranges like YTD, MTD, Last month, Last 30 days, Last 12 Months, and custom date range.
  2. When the user selects the custom date option, a another two slicer will enable to allow them to specify the start and end dates.
  3. Then, we want to filter the "Content" table based on true or false filter indicating whether each "ID_date" from "Content" table falls within the selected range or not. if fall in date range = true if not = falls.

Total 3 sclier needed one is for date range and other 2 are start date and end date. 

start date and end date sclicer shoul be date picker.

 

Please help me to get this 

Thansks in advance 🙂

 

@Greg_Deckler @amitchandak @lbendlin @xifeng_L @Ashish_Mathur Anonymous 

General Comment
Interesting Links
Need Help
Tutorial Requests

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    PowerBiTech , For this first create a date table, then add a calculated column in content table to check if value falls in date table or not using formula

     

    InSelectedDateRange =
    IF (
    ISFILTERED(DateTable[Date]) &&
    Content[ID_date] >= MIN(DateTable[Date]) &&
    Content[ID_date] <= MAX(DateTable[Date]),
    TRUE,
    FALSE
    )

     

     after this create a slicer 

     

    DateRanges =
    DATATABLE (
    "DateRange", STRING,
    {
    {"YTD"},
    {"MTD"},
    {"Last Month"},
    {"Last 30 Days"},
    {"Last 12 Months"},
    {"Custom"}
    }
    )

     

    Create one more measure to handle data range logic

    SelectedRange =
    SWITCH (
    SELECTEDVALUE(DateRanges[DateRange]),
    "YTD", DATESYTD(DateTable[Date]),
    "MTD", DATESMTD(DateTable[Date]),
    "Last Month", DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -1, MONTH),
    "Last 30 Days", DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -30, DAY),
    "Last 12 Months", DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -12, MONTH),
    "Custom", FILTER(DateTable, DateTable[Date] >= SELECTEDVALUE(DateTable[Date]) && DateTable[Date] <= SELECTEDVALUE(DateTable[Date])),
    ALL(DateTable)
    )

    Use this measure to filter the content table based on selected date ranges 

    InRangeContent =
    CALCULATE (
    COUNTROWS(Content),
    KEEPFILTERS(SelectedRange),
    FILTER (
    Content,
    Content[ID_date] IN SelectedRange
    )
    )

     

    Total 3 sclier needed one is for date range and other 2 are start date and end date. :- 

    1. Date Range Slicer: Add a slicer for your DateRanges table.
    2. Start Date Slicer: Add a slicer for the start date from the DateTable.
    3. End Date Slicer: Add a slicer for the end date from the DateTable.
  • PowerBiTech's avatar
    PowerBiTech
    Frequent Visitor
    2 years ago

    bhanu_gautam thank you 

    Getting this error when trying to create selectedrange measure 

    The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

    InSelectedDateRange is showing only false for total count. and i applied this filter in page level.