Show days before the selected date

[Anonymous]

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.youtube.com/watch?v=MooZofz5GOI 
The only thing I dont get is how can I do the number of days measure such that when u put it in the measure, it would actually acount the days and would let you choose number of days like in the picture: 

https://www.youtube.com/watch?v=MooZofz5GOI

[v-yingjl · Accepted solution]

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]

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

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.

[Anonymous]

Dude I can't thank you enough. Thank you so much !!

[Pragati11]

Hi @Anonymous ,

Have you looked into what-if parameters in power BI? These allow user to select a custom value and use it in the calculations.

Check following article for same:

https://radacad.com/power-bi-what-if-parameters

Thanks,

Pragati

[Anonymous]

@Pragati11 Hi, thanks for sharing it, seems very interesting, just the question what can I make of this for my issue? would u miind elaborating a bit?

[Pragati11]

Hi @Anonymous ,

Apologies I think I mis-understood your query. Is it possible for you to add more description by showing some sample data rows and what is the expected calculation output?

Thanks,

Pragati