Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
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
v-juanli-msft
Community Support
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,

Capture19.JPG

3.create a measure in your table

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

Capture18.JPG

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.

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
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,

Capture19.JPG

3.create a measure in your table

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

Capture18.JPG

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

1.PNG

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.