cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Community Support

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.
7 REPLIES 7
Community Support

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.
Community Support

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:

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

Hi,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.