Show Data in frequency of 30 days

[Anonymous]

Hi All,

In my report i have a refresh date column which will have list of dates at which refresh happened. initially i want to show data for only latest 30 days to user. foe ex- if latest refresh date is 12/05/2020, data should be from 12/04/2020 to 12/05/2020. 

I will have this refresh date column as slicer . whenever user clicks on particular date in this slicer, i should get data for 30 days from date of selection(if user clicks feb15, data should be from feb15-Mar14) and in other slicer i should get only dates between selected date and 30 days date(feb15-mar14 dates)( This slicer is for if user want to see single day data).

Please help me to solve this.

TIA

[v-alq-msft · Accepted solution]

@Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

DateTable:

DateTable = DISTINCT('Table'[Refresh Date])

You may create two measures as below.

Isdisplay = 
var _date = SELECTEDVALUE('Table'[Refresh Date])
var _latestdate = 
CALCULATE(
    MAX('Table'[Refresh Date]),
    ALL('Table')
)
return
IF(
    DATEDIFF(_date,_latestdate,DAY)<=31,
    1,
    0
)

Visual control = 
var _selecteddate = SELECTEDVALUE(DateTable[Refresh Date])
var _refreshdate = SELECTEDVALUE('Table'[Refresh Date])
return
IF(
    ISFILTERED(DateTable[Refresh Date]),
    IF(
        DATEDIFF(_selecteddate,_refreshdate,DAY)<=31&&
        DATEDIFF(_selecteddate,_refreshdate,DAY)>0,
        1,
        0
    ),
    1
)

You need to put the measure in the visual level filter to display the result. 'Isdisplay' measure is to show data for only latest 30 days. 'Visual control' is to get data for 30 days from date of selection. Then you may use the 'Refresh Date' from DateTable to filter the visual with 'Visual Control'.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Here is another example (attached)

[Anonymous]

@Greg_Deckler 

 Can you please explain what your logic is doing here. I mean how your power BI file explains my requirment

[Greg_Deckler]

Your requirement is that you need to display certain information in a visualization based upon a complex selection criteria. That is what those two PBIX files do. If you want something very specific to your situation, please provide sample data and expected output.

[Greg_Deckler]

You would need something like a Complex Selector. See attached.