Filter table by previous date

[Anonymous]

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.

Previous Average Event Time =

CALCULATE(

        AVERAGE('Runtime Info'[Event Time]),

        FILTER('Runtime Info', RELATED('Run Info'[Previous Date]) = 'Runtime Info'[Date])

)

 

What am I missing here? I was sure it would work for some reason. Thank you in advance!

[Mariusz]

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.
LinkedIn

[Anonymous]

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

[Anonymous]

Hi @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?

[amitchandak · Accepted solution]

@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])))

[Anonymous]

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!

[Anonymous]

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