Forum Discussion
Find the latest 2 dates
Expected output
| CALCULATIONDATETIME | INVNETSERIALID | GENERATORHOURS |
| 9/25/2020 | CVS-002 | 8154 |
| 11/23/2020 | CVS-002 | 8359 |
| 5/13/2021 | CVS-003 | 9122 |
| 5/27/2021 | CVS-003 | 9159 |
I have this table, I need just filter the 2 latest dates for each inventserialID having an existing Generator hours.
2 LAST DATES =
Var _invent = FIRSTNONBLANK('Table'[INVENTSERIALID],"")
vAR _latestdate = CALCULATE(max('Table'[CALCULATIONDATETIME]),ALLEXCEPT('Table','Table'[INVENTSERIALID]))
vAR _2ndlatest = MAXX(FILTER(filter('Table','Table'[INVENTSERIALID]=_invent),'Table'[CALCULATIONDATETIME]<_latestdate),'Table'[CALCULATIONDATETIME])
RETURN
IF('Table'[CALCULATIONDATETIME] IN {_latestdate,_2ndlatest},'Table'[CALCULATIONDATETIME])
The above worked but does not take the generator hours that has existing value in it, just takes max and 2nd max date for each serialID. Can someone tell me what changes need to be made.
I require last and second last date for each serialid having an existing genratorhours( highlighted in yellow).
axk180022 and here is the output:
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
7 Replies
axk180022 and here is the output:
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
CALLACTIONDATETIME INVENTSERIALID GENERATORHOURS 6/23/2020 14:55 CVS-002 2600 6/24/2020 12:31 CVS-002 7749 6/25/2020 17:32 CVS-002 7749 7/28/2020 13:10 CVS-002 7749 8/19/2020 18:30 CVS-002 0 8/31/2020 13:15 CVS-002 0 9/19/2020 14:00 CVS-002 8103 9/25/2020 21:30 CVS-002 8154 11/18/2020 17:00 CVS-002 0 11/23/2020 19:40 CVS-002 8359 12/16/2020 13:30 CVS-003 8479 3/9/2021 16:43 CVS-003 8804 4/27/2021 21:55 CVS-003 5107 5/13/2021 15:41 CVS-003 9122 5/27/2021 14:00 CVS-003 9159 6/16/2021 19:07 CVS-003 0
axk180022 try this measure:
Measure 2 = CALCULATE ( SUM ( Inv[GENERATORHOURS] ), KEEPFILTERS ( TOPN ( 2, FILTER ( ALLSELECTED ( Inv ), Inv[INVENTSERIALID] = MAX ( Inv[INVENTSERIALID] ) && Inv[GENERATORHOURS] > 0 ), Inv[CALLACTIONDATETIME], DESC ) ) )✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
easure 2 = CALCULATE ( SUM ( Inv[GENERATORHOURS] ), KEEPFILTERS ( TOPN ( 4, FILTER ( ALLSELECTED ( Inv ), Inv[INVENTSERIALID] = MAX ( Inv[INVENTSERIALID] ) && Inv[GENERATORHOURS] > 0 ), Inv[CALLACTIONDATETIME], DESC ) ) )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
