cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeanso
Frequent Visitor

Last and penultimate consumption by date.

I'm having problems, I can't display the last and penultimate hour meter of consumption of a part using the date and machine as a basis. I'm using the dax below, but it has problems and doesn't completely solve my need.

Date                Part code      Part description      Hourmeter

01/05/2022     PX60             Filter                        1000

01/11/2022     PX60             Filter                        3500

29/12/2022     PX60             Filter                        4000

30/12/2022     PX60             Filter                        5000

below follows the dax, penultimate order date =
IF (
COUNTROWS ( 'f_ConsumoSLP' ) >= 2
&& HASONEVALUE ( f_ConsumoSLP[Part Code]),
CALCULATE (
MIN ( f_ConsumoSLP[Horimeter]),
KEEPFILTERS (
TOPN (
two,
ALL ( f_ConsumoSLP[Application Date]),
CALCULATE ( MAX ( f_ConsumoSLP[Application Date]) ), DESC
)
)
)
)

if I filter for example 12/30/2022 it should return 5000 hours as the last consumption and 4000 hours as the penultimate consumption. if I filter 10/25/2022 to 11/02/2022 it should return 3500 as a maximum and 1000 as a minimum, if I filter 04/01/2022 to 04/02/2022 it should return nothing.

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @jeanso ,

 

According to your description, you want to show the last two data in your visual and can be filtered by the date.

Here are the steps you can refer to :

(1)My test data is the same as yours.

(2)We need to create a date table as a slicer like this:

Date = CALENDAR( FIRSTDATE('Table'[Date]) ,LASTDATE('Table'[Date]))

(3)Then we need to create a measure:

Measure = var _min_slicer_date = MIN('Date'[Date])

var _max_slicer_date = MAX('Date'[Date])

var _code = MAX('Table'[Part code])

var _cur_date = MAX('Table'[Date])

var _t = FILTER( ALLSELECTED('Table') , 'Table'[Date] >= _min_slicer_date && 'Table'[Date] <= _max_slicer_date && 'Table'[Part code] =  _code )

var _t2 = SELECTCOLUMNS(TOPN(2,_t , [Date] ,DESC),"date" , [Date])

return

IF(_cur_date in _t2 ,1,-1)

(4)Then we can put the measure on the "Filter on this visual" and put the fields on the visual and we can meet your need:

vtangjiemsft_0-1675408712052.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

View solution in original post

2 REPLIES 2
v-tangjie-msft
Community Support
Community Support

Hi @jeanso ,

 

According to your description, you want to show the last two data in your visual and can be filtered by the date.

Here are the steps you can refer to :

(1)My test data is the same as yours.

(2)We need to create a date table as a slicer like this:

Date = CALENDAR( FIRSTDATE('Table'[Date]) ,LASTDATE('Table'[Date]))

(3)Then we need to create a measure:

Measure = var _min_slicer_date = MIN('Date'[Date])

var _max_slicer_date = MAX('Date'[Date])

var _code = MAX('Table'[Part code])

var _cur_date = MAX('Table'[Date])

var _t = FILTER( ALLSELECTED('Table') , 'Table'[Date] >= _min_slicer_date && 'Table'[Date] <= _max_slicer_date && 'Table'[Part code] =  _code )

var _t2 = SELECTCOLUMNS(TOPN(2,_t , [Date] ,DESC),"date" , [Date])

return

IF(_cur_date in _t2 ,1,-1)

(4)Then we can put the measure on the "Filter on this visual" and put the fields on the visual and we can meet your need:

vtangjiemsft_0-1675408712052.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

amitchandak
Super User
Super User

@jeanso , New index function is a better option, you can use index 2

 

Continue to explore Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors