Trying to use a slicer to provide a dynamic date in my calculations for Last x days of transactions

[Anonymous]

Hi, Relatively new to Power BI and need some help!

I am trying to get a slicer to provide me with a  day range to add into my calculation as per below:

"Cash Last 30 days", CALCULATE(SUM('Interrogation Table_Categorised transactions'[Cash]),DATESBETWEEN('Interrogation Table_Categorised transactions'[DATE], Max('Interrogation Table_Categorised transactions'[DATE]) - 'A1 PeriodOptions'[PeriodChoice], Max('Interrogation Table_Categorised transactions'[DATE])))

Am trying to have the period (either 30, 90, 180 days) to be sourced from a table called
'A1 PeriodOptions'[PeriodChoice] where there is a measure

PeriodChoice = SELECTEDVALUE('A1 PeriodOptions'[PeriodOptions], 30)

My problem is that the calculation is not changing when i choose a day range of anything other than 30 days?
Can anyone help and point me in the right direction

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

As tested, i can get last n days values correctly.

1. create a calendar table, connect it with your table based on "date" column

calendar = CALENDARAUTO()

2.create a what-if parameter,

3.create a measure in your table

Cash Last n days = CALCULATE(SUM(Sheet5[value]),DATESINPERIOD('calendar'[Date],TODAY(),-[parameter select days Value],DAY))

Best Regards
Maggie

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

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

There are some useful links you could refer to:

http://analyticsavenue.com/power-bi-timeperiod-slicer-for-last-7-dayslast-30-days/

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-power-bi.html

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

In addition, for your formula, make sure it is a measure, not a column, 

'A1 PeriodOptions' table should have no relationship with 'Interrogation Table_Categorised transactions',

You could try this measure

[PeriodChoice]=selectedvalue('A1 PeriodOptions'[PeriodOption])

Best Regards
Maggie

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

[Anonymous]

I'm having trouble with recreating you problem in my own workbook, so if the following does not work can you then provide some more information like relationships and a little snippet of your fact table?

But it might be beacuse you are not using an actual date table in the datesbetween.

If you create a date table with a 1:* relationship to 'Interrogation Table_Categorised transactions' then you should be able to use the following measure:

cash in period = 
VAR __selectedPeriod = SELECTEDVALUE( datefilter[dateFilter]; 30)
VAR __maxDate = 
    CALCULATE(
        LASTDATE( 'date'[Date]);
        FILTER(
            'date';
            'date'[Date] <= MAX( 'Interrogation Table_Categorised transactions'[Date])
        )
    )

RETURN
    CALCULATE(
        SUM( 'Interrogation Table_Categorised transactions'[Cash]);
        DATESBETWEEN(
            'date'[Date];
            __maxDate - __selectedPeriod;
            __maxDate
        )
    )

[Anonymous]

Hi, I have returedn to this problem of mine after a period of time.
This works for when everyone has the same dates  in transactions. However, I have a set of data whwere each customer has 12 mths of data, but all with differenct start dates. How can I use this answer you provided to provide:
Summary results across all customers of their latest 30/90/180/365 day period - so  taking out the  impact of a different start date.
I have done a formula as per below but it isnt acccounting for a customers different start date:

All Expenditure =

CALCULATE( 'Key Measures'[All Expenses],

    FILTER( 'Transaction Dates',

        'Transaction Dates'[DATE].[Date] <= Max('Transaction Dates'[DATE]) && 'Transaction Dates'[Date] >= Max('Transaction Dates'[DATE]) - [Total Days]))

The "Total Days" is

Total Days =

IF( HASONEVALUE( 'Date Ranges'[TimeFrame] ), VALUES( 'Date Ranges'[Days] ), COUNTROWS('Transaction Dates' ) )

Time frame ref table is

Any help would be much appreciated

M

[Anonymous]

Just to point out that if I replace the  'A1 PeriodOptions'[PeriodChoice] in the DAX and put in -30  or  -90,, then the result pulls through fine. I  just want to be able to use a slicer with the various  no.days as an option ( ie 30,90,180, 365) so I can see what Cash or other measure might total in the last x no. days.
Seems simple but I cant make it work without resorting to the manual over ride above
Cheers

M

[Anonymous]

An issue could be that SELECTEDVALUE() will return the alternate result if multiple values or no values are selected. Try and make a card visual where you input the measure SELECTEDVALUE('A1 PeriodOptions'[PeriodChoice], 30) just to see if the issue might not work as intended.

Does the table 'A1 PeriodOptions' have any relations to other tables?

[Anonymous]

 Hi,

I made a card amd it filters fine with the slicer in action.
There is no relationship to any other tables