topic Re: Show last worked hours and penultimate hours according to equipment, date and part code in DAX Commands and Tips

Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Tue, 19 Jul 2022 12:39:55 GMT

Hello ,
I have a small problem, apparently easy.
I always need to look for the last and penultimate worked hours taking into account equipment and worked hours.
For example, if you filter the date from 04/14/2022 to 04/15/2022, it is usual to show the last worked hours of 150 hours for equipment 002, but I need the penultimate worked hours to be displayed, which in this case would be 100 hours on 01/01/2022 but I can't get this to work, does anyone have any light for this case.

Re: Show last worked hours and penultimate hours according to equipment, date and part code

tamerj1 — Tue, 19 Jul 2022 13:07:47 GMT

Hi jeanbinhozouza
Just to cinfirm, you are slicing by PART CODE and filtering by DATE. And for each PART CODE you want to display the penultimate WORKED HOURS? If so please try

= VAR TableTopTwoDates = TOPN ( 2, TableName, TableName[DATE] ) VAR PenultimateDate = MINX ( TableTopTwoDates, TableName[DATE] ) VAR PenultimateRow = FILTER ( TableTopTwoDates, TableName[DATE] = PenultimateDate ) RETURN MAXX ( PenultimateRow, TableName[WORKED HOURS] )

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Tue, 19 Jul 2022 13:49:05 GMT

HELLO, EXACTLY THAT, A POINT THAT I NEED TO CHECK, IF FOR EXAMPLE FILTER 10/01 SHOULD ONLY APPEAR EQUIPMENT 1 AND PART 1XX10, IF THERE IS ONLY ONE VALUE THAT HAPPENS IN THE FILTERED CASE, CAN I NOT SHOW THE MINIMUM AS IT WILL ONLY HAVE THE MAXIMUM?

Re: Show last worked hours and penultimate hours according to equipment, date and part code

tamerj1 — Tue, 19 Jul 2022 13:55:24 GMT

jeanbinhozouza
Please try

= VAR CurrentDate = SELECTEDVALUE ( TableName[DATE] ) VAR TableTopTwoDates = TOPN ( 2, TableName, TableName[DATE] ) VAR PenultimateDate = MINX ( TableTopTwoDates, TableName[DATE] ) VAR PenultimateRow = FILTER ( TableTopTwoDates, TableName[DATE] = PenultimateDate ) RETURN IF ( CurrentDate <> PenultimateDate, MAXX ( PenultimateRow, TableName[WORKED HOURS] ) )

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Tue, 19 Jul 2022 19:11:22 GMT

tamerj1 sorry to be answering again, but this problem persists, the errors that are happening with your measurements I also faced, when arranging for a function does not serve another.
again it is showing an error, this time it no longer shows the penultimate hour meter as I needed...

Re: Show last worked hours and penultimate hours according to equipment, date and part code

tamerj1 — Tue, 19 Jul 2022 19:16:53 GMT

jeanbinhozouza
Yes, it is different now as you are using a date table. Please copy/paste the code here so I can edit

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Tue, 19 Jul 2022 19:25:52 GMT

Follow the measures, if you need any other information let me know please

as it needs repair:

TESTEMINX = VAR CurrentDate =

SELECTEDVALUE ( Calendar_1[Date] )

VAR TableTopTwoDates =

TOPN ( 2, Calendar_1, Calendar_1[Date])

VAR PenultimateDate =

MINX ( TableTopTwoDates, Calendar_1[Date] )

VAR PenultimateRow =

FILTER ( TableTopTwoDates, Calendar_1[Date] = PenultimateDate )

VAR WORKED = MINX(Teste,Teste[WORKED HOURS])

RETURN

IF (

CurrentDate <> PenultimateDate,

MINX ( PenultimateRow, WORKED )

)

measure for the maximum worked hours:

MAXX_ =

VAR TableTopTwoDates= TOPN (2,Calendar_1,Calendar_1[Date])

VAR PenultimateDate =

MINX ( TableTopTwoDates, Calendar_1[Date] )

VAR PenultimateRow =

FILTER ( TableTopTwoDates, Calendar_1[Date] = PenultimateDate )

VAR MaxxWorked = MAXX(Teste,Teste[WORKED HOURS])

RETURN

MAXX(PenultimateRow, MaxxWorked)

Re: Show last worked hours and penultimate hours according to equipment, date and part code

tamerj1 — Tue, 19 Jul 2022 19:33:11 GMT

jeanbinhozouza
Please try

TESTEMINX = VAR CurrentDate = CALCULATE ( SELECTEDVALUE ( Calendar_1[Date] ) ) VAR TableTopTwoDates = TOPN ( 2, CALCULATETABLE ( Calendar_1 ), Calendar_1[Date] ) VAR PenultimateDate = MINX ( TableTopTwoDates, Calendar_1[Date] ) VAR PenultimateRow = FILTER ( TableTopTwoDates, Calendar_1[Date] = PenultimateDate ) VAR WORKED = MINX ( Teste, Teste[WORKED HOURS] ) RETURN IF ( CurrentDate <> PenultimateDate, MINX ( PenultimateRow, WORKED ) )

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Tue, 19 Jul 2022 19:34:50 GMT

If you need any more information, please let me know: Below is the measure of MIN worked hours that needs adjustments.

TESTEMINX = VAR CurrentDate =

SELECTEDVALUE ( Calendar_1[Date] )

VAR TableTopTwoDates =

TOPN ( 2, Calendar_1, Calendar_1[Date])

VAR PenultimateDate =

MINX ( TableTopTwoDates, Calendar_1[Date] )

VAR PenultimateRow =

FILTER ( TableTopTwoDates, Calendar_1[Date] = PenultimateDate )

VAR WORKED = MINX(Teste,Teste[WORKED HOURS])

RETURN

IF (

CurrentDate <> PenultimateDate,

MINX ( PenultimateRow, WORKED )

)

Measure of worked MAX, working.

MAXX_ =
VAR TableTopTwoDates= TOPN (2,Calendar_1,Calendar_1[Date])

VAR PenultimateDate =
MINX ( TableTopTwoDates, Calendar_1[Date] )
VAR PenultimateRow =
FILTER ( TableTopTwoDates, Calendar_1[Date] = PenultimateDate )
VAR MaxxWorked = MAXX(Teste,Teste[WORKED HOURS])
RETURN
MAXX(PenultimateRow, MaxxWorked)

Re: Show last worked hours and penultimate hours according to equipment, date and part code

tamerj1 — Tue, 19 Jul 2022 19:35:59 GMT

jeanbinhozouza
Otherwise, please try this version as well

TESTEMINX = VAR CurrentDate = CALCULATE ( SELECTEDVALUE ( Calendar_1[Date] ), CROSSFILTER ( Calendar_1[Date], Teste[Date], BOTH ) ) VAR TableTopTwoDates = TOPN ( 2, CALCULATETABLE ( Calendar_1, CROSSFILTER ( Calendar_1[Date], Teste[Date], BOTH ) ), Calendar_1[Date] ) VAR PenultimateDate = MINX ( TableTopTwoDates, Calendar_1[Date] ) VAR PenultimateRow = FILTER ( TableTopTwoDates, Calendar_1[Date] = PenultimateDate ) VAR WORKED = MINX ( Teste, Teste[WORKED HOURS] ) RETURN IF ( CurrentDate <> PenultimateDate, MINX ( PenultimateRow, WORKED ) )

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Tue, 19 Jul 2022 19:45:32 GMT

test, but it doesn't work, it only works without the date filters as shown, with the date filters it doesn't work, I took advantage and looked at the relationships but they are correct.
I've had this problem for a few days and it always gets lost in this part.
Is there still a way around this? First of all, I'm grateful for the answers.

Re: Show last worked hours and penultimate hours according to equipment, date and part code

tamerj1 — Tue, 19 Jul 2022 19:48:52 GMT

jeanbinhozouza
Have you tried the formula with the CROSSFILTER? As I replied with two formulas.

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Tue, 19 Jul 2022 19:53:40 GMT

yes, I tried and it doesn't run as expected with date filters active.

Re: Show last worked hours and penultimate hours according to equipment, date and part code

tamerj1 — Tue, 19 Jul 2022 20:05:26 GMT

jeanbinhozouza
Can you please share a screenshot of the data model?

Re: Show last worked hours and penultimate hours according to equipment, date and part code

Anonymous — Wed, 20 Jul 2022 01:35:08 GMT

Step 1: Create a Calculated column

Rank_MODEL =

VAR MN = PBI_Comm_Help_1[MODEL] /*Current row ModelName */

RETURN

RANKX( FILTER ( PBI_Comm_Help_1

,PBI_Comm_Help_1[MODEL] = MN

)

,PBI_Comm_Help_1[DATE]

)

Step 2: Create Measure

penultimate_WorkedHours =

VAR WorkedHours = CALCULATE( MAX(PBI_Comm_Help_1[WORKED HOURS])

,PBI_Comm_Help_1[Rank_MODEL] = 2)

RETURN WorkedHours

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Wed, 20 Jul 2022 11:52:16 GMT

Good Morning,
Follow the prints.

Measures:

MAXX_ =

VAR TableTopTwoDates= TOPN (2,Calendar_1,Calendar_1[Date])

VAR PenultimateDate =

MINX ( TableTopTwoDates, Calendar_1[Date] )

VAR PenultimateRow =

FILTER ( TableTopTwoDates, Calendar_1[Date] = PenultimateDate )

VAR MaxxWorked = MAXX(Teste,Teste[WORKED HOURS])

RETURN

MAXX(PenultimateRow, MaxxWorked)

Measure MIN:

TESTEMINX =

VAR CurrentDate =

CALCULATE (

SELECTEDVALUE ( Calendar_1[Date] ),

CROSSFILTER ( Calendar_1[Date], Teste[Date], BOTH )

)

VAR TableTopTwoDates =

TOPN (

CALCULATETABLE (

Calendar_1,

CROSSFILTER ( Calendar_1[Date], Teste[Date], BOTH )

Calendar_1[Date]

)

VAR PenultimateDate =

MINX ( TableTopTwoDates, Calendar_1[Date] )

VAR PenultimateRow =

FILTER ( TableTopTwoDates, Calendar_1[Date] = PenultimateDate )

VAR WORKED =

MINX ( Teste, Teste[WORKED HOURS] )

RETURN

IF ( CurrentDate <> PenultimateDate, MINX ( PenultimateRow, WORKED ) )

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Wed, 20 Jul 2022 11:59:58 GMT

Good Morning Anonymous ,
I followed the steps we have the same problem, it doesn't work with active date filters.
Alias I also tried to insert REMOVEFILTERS, but without success.

Re: Show last worked hours and penultimate hours according to equipment, date and part code

Anonymous — Wed, 20 Jul 2022 18:16:23 GMT

measure :

penultimate_WorkedHours =

VAR cEQ = SELECTEDVALUE(PBI_Comm_Help_1[MODEL])

VAR Tab1 = FILTER( FILTER( ALL(PBI_Comm_Help_1)

,PBI_Comm_Help_1[MODEL] = cEQ

)

,PBI_Comm_Help_1[Rank_MODEL] = 2

)

VAR WorkedHours = CALCULATE( MAX(PBI_Comm_Help_1[WORKED HOURS])

,Tab1

)

RETURN WorkedHours

Calculated Column :

Rank_MODEL =

VAR MN = PBI_Comm_Help_1[MODEL] /*Current row ModelName */

RETURN

RANKX( FILTER ( PBI_Comm_Help_1

,PBI_Comm_Help_1[MODEL] = MN

)

,PBI_Comm_Help_1[DATE]

)

Does this Work for you ?

Re: Show last worked hours and penultimate hours according to equipment, date and part code

tamerj1 — Thu, 21 Jul 2022 17:21:44 GMT

Hi jeanbinhozouza
This is the final solution with sample file https://www.dropbox.com/t/3n9vGHK8MXeC5iso

MAXX_ = VAR MaxDate = CALCULATE ( MAX ( Teste[DATE] ), USERELATIONSHIP ( 'Calendar_1'[Date], Teste[DATE] ) ) VAR TableOnAndBefore = FILTER ( Teste, Teste[DATE] <= MaxDate ) VAR TableTopTwoDates = TOPN ( 2, TableOnAndBefore, Teste[Date] ) VAR Last2Value = SUMX ( TableTopTwoDates, Teste[WORKED HOURS] ) VAR MaxDateTable = FILTER ( TableTopTwoDates, Teste[Date] = MaxDate ) VAR LastValue = SUMX ( MaxDateTable, Teste[WORKED HOURS] ) VAR PenultimateValue = Last2Value - LastValue VAR Result = PenultimateValue - LastValue RETURN Result

Re: Show last worked hours and penultimate hours according to equipment, date and part code

jeanbinhozouza — Thu, 21 Jul 2022 17:23:48 GMT

very good thanks.