Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |