DAX Filter on dates

[Anonymous]

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 over, i.e betweem Aug 5th 2019 to Sep 5th 2019.  

Given the selected start and end period that the user choose to analyse over, using the slicer. I need to create a meaure that records the market value at the start date and end date periods. Below is an example of some dates and data:

Date	MV
22/04/2019	22
22/04/2019	3423
15/06/2019	2354
01/07/2019	-456
14/09/2019	45

I used the following code in my measures

Start Date = FIRSTDATE(test[Date])  <- This works and changes as the slicer date range changes

First Settlement = SUMX(FILTER(test, test[Date] = [Start Date] ), test[MV])  <- Doesn't work and returns the sum of ALL of the market values (5388)

I used the same code premis on the following table and got the right answer...

Code	MV
A	22
A	3423
B	2354
C	-456
D	45

First Settlement = SUMX(FILTER(test, test[Code] = "A" ), test[MV])    returns 3445

Help me please!

[Nathaniel_C · Accepted solution]

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]

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