Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Show days before the selected date

Hey guys, I am basically trying to get the calculations so that once the date is chosen in the filter, I could show particular number of days back. This is the idea I got from this guy : https://www....
Need Help
Show and Tell
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    To achieve the simliar result from the viedo, you need to create what-if parameter first:

    It will produce a table with a column, a measure and a slicer which includes the column, just rename them to show the number:

    Then you need a separate date table and use it as another slicer, if your source table has already had date columns, you can use CALENDARAUTO(), DISTINCT() to create it.

    Create a Date selected measure:

    Date selected = SELECTEDVALUE('Date'[Date])

    Create a total measure like this and put it in the bar chart:

    Total =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
            && 'Table'[Date]
                >= SELECTEDVALUE ( 'Date'[Date] ) - [Day Number]
    )
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

v-yingjlDude thats sick, thank you so much. Also wondering if you will know this : 
In one of the report pages I always display data of the current month up to to day, any idea how can I make it that the last day chosen in the filter would give all the days up to date?

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

You can use CALENDAR() to modify your date table, use Today() as the ending date parameter:

Table 2 = CALENDAR("2021/1/1",TODAY())

So that each time you open the report, the last date in the slicer will be the latest day.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.