Forum Discussion
Custom Formula to mark Data range
I have a request to display the rolling last 15, 30, 45, 90, 120 and higher on multiple visuals within a single page. So, one visual should display the last 15 days, another visual to display the last 45 days etc.. So , my idea is to create a custom column within the Date which will mark all those days within the 15 days as 15 and with 30 days as 30 etc.
Any recommendation on how to achieve that. I do have a date table and can create a custom column if needed , but not sure if creating a custom column would be the approach or a measure . Please advise on the solution to achieve this.
2 Replies
PBI5851 , beast is relative date slicer.
If nor you need create a slicer using a table (enter date) and you will also need an independent date table to display of trend(as rolling will sum up data into one date)
example
Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))
Refer a similar solution in the past:https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629
This video how to display trend when data rollup into one date https://www.youtube.com/watch?v=duMSovyosXE
Measure slicer additional reference: https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
that's a reasonable approach.
Create calculated columns like so
InLast15 = TODAY()-dates[Date]<=15
InLast30 = TODAY()-dates[Date]<=30
and so on. Then use these columns in your measure filters.
Note that this pretty much assumes that your dataset is refreshed daily.
