Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Timeperiod slicer

Hi Folks ..

I want to create a slicer where the data is sorted by Year,Month,Week,Day wise so there is a default visual in Power BI Iused but the issue is I just want past and present data to be filtered but <refer the img below>

so here i want to control the slicer to show only last and this options excluding next .

How to acheive this or any other possible ways please kindly suggest .

 

Thanks .

LivioLanzo  Saud1999 saud968 

Tips and Tricks

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks Bud , those videos helped to proceed in different way .

  • saud968's avatar
    saud968
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Create a New Table:

    Go to the "Model" view in Power BI Desktop.
    Create a new table by clicking on "New Table" in the "Model" ribbon.
    Define the Table with Slicer Options:

    Create a table that includes the following values: "Last Month" and "Present Month."
    DAX

    SlicerTable = UNION(
    ROW("DateRange", "Last Month"),
    ROW("DateRange", "Present Month")
    )
    Create a Relationship:

    Ensure that there is a relationship between the new "DateRange" column in your slicer table and the appropriate column in your main data table (e.g., date column).
    Create a Slicer Visualization:

    Drag the "Slicer" visualization onto your report canvas.
    Drag the "DateRange" column from your slicer table into the "Field" well of the slicer.
    Filter Data with DAX:

    Create a measure that uses DAX to filter your data based on the selected value in the slicer.
    DAX

    FilteredData =
    SWITCH (
    SELECTEDVALUE('SlicerTable'[DateRange]),
    "Last Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()) - 1 && 'YourData'[Date] < STARTOFMONTH(TODAY())),
    "Present Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()))
    )
    Replace 'YourData' with the actual name of your main data table. 

    Let me know if this helps. 

    If I have posted a response that resolves your question, please accept it as a solution to close the post.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks for the reply ,

      And I apologise for asking silly question , but I'm facing error  while creating measure 

      FilteredData =
      SWITCH (
      SELECTEDVALUE('SlicerTable'[DateRange]),
      "Last Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()) - 1 && 'YourData'[Date] < STARTOFMONTH(TODAY())),
      "Present Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()))
      )

      For this mesaure I got few errors which I tried to resolve but still found new errors 
      Error : A function 'STARTOFMONTH' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

      I tried one more :

      CurrentDate=Today()

      FilteredData =
      SWITCH (
      SELECTEDVALUE('SlicerTable'[DateRange]),
      "Last Month", CALCULATETABLE(
      FILTER(CalendarTable, 'CalendarTable'[Date] >= STARTOFMONTH(CalendarTable[CurrentDate]) - 1 && 'CalendarTable'[Date] < STARTOFMONTH(CalendarTable[CurrentDate]))
      ),
      "Present Month", CALCULATETABLE(
      FILTER(CalendarTable, 'CalendarTable'[Date] >= STARTOFMONTH(CalendarTable[CurrentDate]))
      )
      )

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

       

      Even this also I tried :

      StartOfMonth = STARTOFMONTH(CalendarTable[Date])

      FilteredData =
      SWITCH (
      SELECTEDVALUE('SlicerTable'[DateRange]),
      "Last Month", CALCULATETABLE(
      FILTER(CalendarTable, [Date] >= [StartOfMonth] - 1 && [Date] < [StartOfMonth])
      ),
      "Present Month", CALCULATETABLE(
      FILTER(CalendarTable, [Date] >= [StartOfMonth])
      )
      )

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

       

      Can u please suggest me more ...??

      Thanks .

      • saud968's avatar
        saud968
        Icon for Memorable Member rankMemorable Member
        2 years ago

        No worries, I am also in a learning phase and not as expert as others but I will try to help you as much as possible. 

        you have a table named 'CalendarTable' with a column 'Date' and a slicer table named 'SlicerTable' with a column 'DateRange,' you can create the following measures:

        DAX

        CurrentDate = TODAY()

        FilteredData =
        SWITCH (
        SELECTEDVALUE('SlicerTable'[DateRange]),
        "Last Month",
        CALCULATETABLE(
        YourData,
        FILTER(
        'CalendarTable',
        'CalendarTable'[Date] >= STARTOFMONTH(TODAY()) - 1 &&
        'CalendarTable'[Date] < STARTOFMONTH(TODAY())
        )
        ),
        "Present Month",
        CALCULATETABLE(
        YourData,
        FILTER(
        'CalendarTable',
        'CalendarTable'[Date] >= STARTOFMONTH(TODAY())
        )
        )
        )
        Make sure to replace 'YourData' with the actual name of your main data table. This measure filters your data based on the selected value in the 'SlicerTable' slicer, considering the "Last Month" and "Present Month" options.

        Note: The 'YourData' table should have a relationship with the 'CalendarTable' on the date column.