Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

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

Mariusz's avatar
Mariusz
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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-201910
    28-04-201912

    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