Forum Discussion
Filter table by previous date
Hello everyone!
I am new to DAX but I have a situation here that I thought I got right but apparently I didn't. I have two tables one Run Info and another Runtime Info. Both have Date columns but Run Info also has a Previous Date column which basically gets the previous row of Date like shown bellow:
Date Previous Date
3/3/2020
4/4/2020 3/3/2020
What I want to do is get the current Date from a matrix visualisation, get the Previous Date of that Date from Run Info, Filter Runtime Info by Previous Date and get the Average of a column called Event Time. I've created the measure bellow, but it doesn't seem to return any data to the visualization.
What am I missing here? I was sure it would work for some reason. Thank you in advance!
Anonymous
Use a Date calendar
If you have continuous dates
Day behind Sales = CALCULATE(AVERAGE('Runtime Info'[Event Time]),dateadd('Date'[Date],-1,Day))
If the last date is not -1 day, last date with Data
Last Day Non Continous = CALCULATE(AVERAGE('Runtime Info'[Event Time]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
6 Replies
Anonymous
Use a Date calendar
If you have continuous dates
Day behind Sales = CALCULATE(AVERAGE('Runtime Info'[Event Time]),dateadd('Date'[Date],-1,Day))
If the last date is not -1 day, last date with Data
Last Day Non Continous = CALCULATE(AVERAGE('Runtime Info'[Event Time]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
Hi amitchandak
**bleep** the second one worked!!! Thank you so much!! If it isn't too much of a trouble, do you have any idea why the one I did, didn't work? The calculated column that created the previous date worked like this:
Previous Date = CALCULATE(
MAX('Run Info'[Run Date]),
FILTER('Run Info','Run Info'[Run Date]<EARLIER('Run Info'[Run Date]))
)
Thank you either way though. Your solution worked!How you could get the last record of the month until the selected date.
Example:
I have the following table:
Date Amount 05-01-2019 20 23-01-2019 15 15-02-2019 30 12-03-2019 10 24-03-2019 5 04-04-2019 15 13-04-2019 10 28-04-2019 12 and I select in a date filter the date 15-04-2019
Then I wish I could get the following table:
one-19 feb-19 mar-19 Apr-19 Amount 15 30 5 10 As I can do this, since I only manage to get the last data of the month or the last record of the month of the selected date but not both.
I hope you can help me, greetings and thank you in advance
Hi Anonymous
Try this
Previous Average Event Time = VAR __previusDate = SELECTEDVALUE( 'Run Info'[Previous Date] ) RETURN CALCULATE( AVERAGE( 'Runtime Info'[Event Time] ), ALL( 'Runtime Info' ), 'Runtime Info'[Date] = __previusDate )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedInHi Mariusz
Thank you for your reply! I tried it and I got the following data. So something good is happening but it's not quite there yet. Any thoughts?How you could get the last record of the month until the selected date.
Example:
I have the following table:
Date Amount 05-01-2019 20 23-01-2019 15 15-02-2019 30 12-03-2019 10 24-03-2019 5 04-04-2019 15 13-04-2019 10 28-04-2019 12 and I select in a date filter the date 15-04-2019
Then I wish I could get the following table:
one-19 feb-19 mar-19 Apr-19 Amount 15 30 5 10 As I can do this, since I only manage to get the last data of the month or the last record of the month of the selected date but not both.
I hope you can help me, greetings and thank you in advance
