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

[jeanbinhozouza]

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.

[tamerj1 · Accepted solution]

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

[jeanbinhozouza]

very good thanks.

[Anonymous]

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

[jeanbinhozouza]

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.

[Anonymous]

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 ?

[jeanbinhozouza]

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)

[tamerj1]

@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 ) )

[tamerj1]

@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 ) )

[jeanbinhozouza]

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.

[tamerj1]

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

[jeanbinhozouza]

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 (

        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 ) )

[tamerj1]

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

[jeanbinhozouza]

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

[tamerj1]

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] )

[jeanbinhozouza]

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?

[tamerj1]

@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] )
    )

[jeanbinhozouza]

@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...

[tamerj1]

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

[jeanbinhozouza]

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)