Forum Discussion

PANNEER's avatar
PANNEER
Frequent Visitor
9 months ago
Solved

year to date slicer

i want a slicer to show from today to last one year and whenever i open the power bi report the date should automatically from current date to last one year. please anyboth help me to resolve this issue in power bi.

DateTable =
VAR _End = TODAY()
VAR _Start = _End - 364   // <-- subtract 364 so the window is the last 365 days ending at _End
RETURN
ADDCOLUMNS(
    CALENDAR( _Start, _End ),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "MonthYear", FORMAT([Date], "MMM"),
    "MonthNumber", MONTH([Date]),
    "MonthYearSort", YEAR([Date]) * 100 + MONTH([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "IsLast365", IF( [Date] >= _Start && [Date] <= _End, 1, 0 )
)
The above measures are already created. please do helpful.
 

 

Need Help
  • alish_b's avatar
    alish_b
    9 months ago

    Hi PANNEER ,

    The relative date slicer should update. Here I have simulated date changes and it is working fine (you will need to close the file and reopen it):

          

     

    I believe the issue in your case is that the date table extends till only today (that is the last date in the date table is set to be TODAY) and does not have full years. In Table definition and calculated columns, the TODAY is not dynamic and needs a refresh to pickup the latest date.

    In measures and the filters it is a different story, TODAY picks up the latest date on runtime/view time.

    So, follow these steps and try checking the desktop report (adjust your sytem date and time to simulate date changes)
    1. Create a date table as follows:

    DateTable =
    VAR _EndYear = YEAR(TODAY())
    VAR _StartYear = YEAR(TODAY()- 364)   // <-- subtract 364 so the window is the last 365 days ending at _End
    RETURN
    ADDCOLUMNS(
        CALENDAR( DATE(_StartYear,1,1) , DATE(_EndYear,12,31) ),
        "Year", YEAR([Date]),
        "Month", FORMAT([Date], "MMM"),
        "MonthYear", FORMAT([Date], "MMM"),
        "MonthNumber", MONTH([Date]),
        "MonthYearSort", YEAR([Date]) * 100 + MONTH([Date]),
        "Quarter", "Q" & FORMAT([Date], "Q")
    )

    2. Connect the date table's Date field to the appropriate fact table field with the date.

    3. Drag the Date field from DateTable to the filters pane (Filters on this page)> Change Filter Type to Relative date> Set it to Show items when the value is in the last 1 years> Make sure the Include today is checked.

     

    And that is it. Change system date time, close and open the report and test.

     

    Note: I would recommed fine tuning the date table, you could set it expand to all the required dates in your data model.

    Hope it helps!

8 Replies

  • alish_b's avatar
    alish_b
    Icon for Super User rankSuper User
    9 months ago

    Hey PANNEER ,

     

    From the look of the date table, it should automatically work, given that the report is refreshed at least once every day. What I am assuming is that you are working in Power BI Desktop, where you set the date slicer and it works great for that day and next day when you open the report it is still stuck at the date range from yesterday to one year back from yesterday (because the model is not refreshed and will work if you hit the Refresh button).

    Now the simplest way to fix it will be to add a relative date slicer. Extend the date table as it is standard to have full years in date tables:

    DateTable =
    VAR _EndYear = YEAR(TODAY())
    VAR _StartYear = YEAR(TODAY()- 364)   // <-- subtract 364 so the window is the last 365 days ending at _End
    RETURN
    ADDCOLUMNS(
        CALENDAR( DATE(_StartYear,1,1) , DATE(_EndYear,12,31) ),
        "Year", YEAR([Date]),
        "Month", FORMAT([Date], "MMM"),
        "MonthYear", FORMAT([Date], "MMM"),
        "MonthNumber", MONTH([Date]),
        "MonthYearSort", YEAR([Date]) * 100 + MONTH([Date]),
        "Quarter", "Q" & FORMAT([Date], "Q"),
        "IsLast365", IF( [Date] >= _Start && [Date] <= _End, 1, 0 )
    )
    Now in the filters pane for the slicer, convert the filter type for DateTable[Date] to Relative date set for last 1 year or number of days per your need (you might want to add it in the filters on this page section if you want all visuals in the page to get affected by this range when the slicer is not used):

    Another way would be create a measure (not a calculated column as they are recomputed only on refresh):

    IsInLast365 = IF(SELECTEDVALUE('Date'[Date]) >= TODAY() - 364 && SELECTEDVALUE('Date'[Date]) <= TODAY(), 1)
    Then, add this measure in the filter section and set the filter to 'is equal to' 1.

     

    Please fine tune these according to your requirement.

    Hope it helps!

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 months ago

    Hi PANNEER ,

     

    Thank you alish_b and ryan_mayu  for the helpful input!


    Were you able to resolve the issue? If the response addressed your query, kindly confirm. This helps keep the community informed and improves solution visibility.


    Thank you for your support!

    • PANNEER's avatar
      PANNEER
      Frequent Visitor
      9 months ago

      No the issue is not resolved yet. when i re open the power bi report in slicer it is not showing me from today's date to last one year

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 months ago

        Hi PANNEER ,

         

        It sounds like what you’re seeing is just the way Power BI handles anything based on dynamic dates. Even when you use a relative date slicer or a column that relies on TODAY, the slicer doesn’t actually shift forward until the model refreshes. That’s why the report looks out of date when you open it again. The easiest approach is to keep the Date column in a slicer and set it to a relative range like Last 365 Days, then refresh the dataset so the window moves forward.

         

        In Desktop that means hitting Refresh when you open the file, and in the Service it usually means setting up a scheduled refresh. If you prefer not to use a slicer in the report, you can also create a simple flag column that marks whether a date falls within the last 365 days and filter your visuals on that. As long as the dataset refreshes regularly, that flag will keep your rolling year view up to date.

         
        Thank you.