Forum Discussion
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.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:
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.
7 Replies
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.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?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.
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
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?
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
