Forum Discussion
Minimun Date from Table
Hello all,
I have the following table:
Dim1 | Dim2 | Dim3 | Measure1 | Measure2 | Measure3 | Date |
Obj1 | Prop1 | New | 2.5 |
|
| 8/1/2021 |
Obj1 | Prop1 | New |
| 2.5 |
| 8/2/2021 |
Obj1 | Prop1 | New |
| 2.5 |
| 8/3/2021 |
Obj2 | Prop1 | Removed |
|
| 3 | 7/20/2021 |
Obj2 | Prop1 | Removed |
|
| 3 | 7/21/2021 |
Obj2 | Prop1 | Removed |
|
| 3 | 7/22/2021 |
My goal is to group by the minimum "Date" and taking the value of the corresponding measure, such as:
Dim1 | Dim2 | Dim3 | Measure1 | Measure2 | Measure3 | Date |
Obj1 | Prop1 | New | 2.5 |
|
| 8/1/2021 |
Obj2 | Prop1 | Removed |
|
| 3 | 7/20/2021 |
If possible I would like to do it by modifying my measures, where the 3 of them is a formula similar to this one:
CALCULATE( SUM([Points_Removed]), FILTER(Facts3,Facts3[Date] >= Facts3[Start Date]), FILTER(Facts3,Facts3[Date] <= Facts3[End Date]) )
Is there any advise you could give me?
Thank you all in advanced!
Hi fonso ,
Here are the steps you can follow：
1. Create measure.
Measure1 = var _mindate=MINX(FILTER(ALL('Table'),'Table'[Dim1]=MAX('Table'[Dim1])&&'Table'[Dim2]=MAX('Table'[Dim2])&&'Table'[Dim3]=MAX('Table'[Dim3])),[Date]) return IF(MAX('Table'[Date])=_mindate, CALCULATE(SUM('Table'[Column1]),FILTER(ALL('Table'),'Table'[Date]=_mindate)),BLANK())Measure2 = var _mindate=MINX(FILTER(ALL('Table'),'Table'[Dim1]=MAX('Table'[Dim1])&&'Table'[Dim2]=MAX('Table'[Dim2])&&'Table'[Dim3]=MAX('Table'[Dim3])),[Date]) return IF(MAX('Table'[Date])=_mindate, CALCULATE(SUM('Table'[Column2]),FILTER(ALL('Table'),'Table'[Date]=_mindate)),BLANK())Measure3 = var _mindate=MINX(FILTER(ALL('Table'),'Table'[Dim1]=MAX('Table'[Dim1])&&'Table'[Dim2]=MAX('Table'[Dim2])&&'Table'[Dim3]=MAX('Table'[Dim3])),[Date]) return IF(MAX('Table'[Date])=_mindate, CALCULATE(SUM('Table'[Column3]),FILTER(ALL('Table'),'Table'[Date]=_mindate)),BLANK())2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hi fonso ,
Here are the steps you can follow：
1. Create measure.
Measure1 = var _mindate=MINX(FILTER(ALL('Table'),'Table'[Dim1]=MAX('Table'[Dim1])&&'Table'[Dim2]=MAX('Table'[Dim2])&&'Table'[Dim3]=MAX('Table'[Dim3])),[Date]) return IF(MAX('Table'[Date])=_mindate, CALCULATE(SUM('Table'[Column1]),FILTER(ALL('Table'),'Table'[Date]=_mindate)),BLANK())Measure2 = var _mindate=MINX(FILTER(ALL('Table'),'Table'[Dim1]=MAX('Table'[Dim1])&&'Table'[Dim2]=MAX('Table'[Dim2])&&'Table'[Dim3]=MAX('Table'[Dim3])),[Date]) return IF(MAX('Table'[Date])=_mindate, CALCULATE(SUM('Table'[Column2]),FILTER(ALL('Table'),'Table'[Date]=_mindate)),BLANK())Measure3 = var _mindate=MINX(FILTER(ALL('Table'),'Table'[Dim1]=MAX('Table'[Dim1])&&'Table'[Dim2]=MAX('Table'[Dim2])&&'Table'[Dim3]=MAX('Table'[Dim3])),[Date]) return IF(MAX('Table'[Date])=_mindate, CALCULATE(SUM('Table'[Column3]),FILTER(ALL('Table'),'Table'[Date]=_mindate)),BLANK())2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Wow! This is great! Thank you 🙂
- Greg_Deckler
Community Champion
fonso Well, you could create a simple measure:
Min Date Measure = MIN(Fact3[Date])
You could then use something like Lookup Min/Max: Lookup Min/Max - Microsoft Power BI Community
