Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Filter on dates

Hello noob BI user here,   I have a large table with a set of dates and reliated market values. I have a slicer set up which allows the user to set the time period they want to analyse the data ove...
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi Anonymous ,

    Is this what you are looking for? I did note that one date was duplicated which I changed as I did not think we could have two market values on same date. 

     

    First date = FIRSTDATE(markv[Date])
    Last Date = LASTDATE(markv[Date])
    Market Value First Date = CALCULATE(MAX(markv[MV]),FILTER(markv,MIN(markv[Date])=[First date]))
    Market Value Last date = CALCULATE(MAX(markv[MV]),FILTER(markv,MAX(markv[Date])=[Last Date]))

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous ,

Is this what you are looking for? I did note that one date was duplicated which I changed as I did not think we could have two market values on same date. 

 

First date = FIRSTDATE(markv[Date])
Last Date = LASTDATE(markv[Date])
Market Value First Date = CALCULATE(MAX(markv[MV]),FILTER(markv,MIN(markv[Date])=[First date]))
Market Value Last date = CALCULATE(MAX(markv[MV]),FILTER(markv,MAX(markv[Date])=[Last Date]))

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you!!!!!!!

 

In this case you can have two market values on the same date so I use sum in place of max:

 

Market Value First Date = CALCULATE(sum(markv[MV]),FILTER(markv,MIN(markv[Date])=[First date]))

Thanks Nathaniel for your help