Help! Calculate the distinct positions considering the last inspection in the month the vehicle

[alexandrem79]

Hello all,

I need help, I'm not able to calculate the distinct positions considering the last inspection of the vehicle in the month.

TABLE:

Fleet	Position	Date	Value
Fleet1	695475-XC	2020-04-05	6.8
Fleet1	695475-XC	2020-04-16	10.2
Fleet1	324567-KQ	2020-04-25	7.4
Fleet1	324567-KQ	2020-04-12	15.5
Fleet1	893765-JD	2020-04-19	8.9
Fleet1	893765-JD	2020-04-28	5.2

FILTERS: Year: 2020 | Month: April
RESULT

Fleet	Total Unique Positions - Over (10) Qty	Total Unique Positions -  Between (7-10) Qty	Total Unique Positions -  Below (7) Qty
Fleet1	1	1	1

I'll be very grateful if anyone can help me!

Regards,
Alexandre Martins

[alexandrem79 · Accepted solution]

I was able to prepare a solution after watching the video on the link below:
Calculate The Last Or Max Value Within A Table Of Data Using DAX In Power BI

Solution:

Measure Max Date Position =
VAR CurrentPosition = SELECTEDVALUE(inspections[Position])

RETURN
MAXX(
FILTER( ALLSELECTED(inspections), inspections[Position] = CurrentPosition),
inspections[Date] )
)

Measure Over 10 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] > 10,
FILTER(inspections, inspections[Date] = [Max Date Position])
)

Measure Between 7-10 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] >= 7,
inspections[Value] <= 10,
FILTER(inspections, inspections[Date] = [Max Date Position])
)

Measure Below 7 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] < 7,
FILTER(inspections, inspections[Date] = [Max Date Position])
)

Thank you all,
Regards!

[v-yingjl]

Hi @alexandrem79 ,

I have created a calculated table to achieve this:

Table = 
SUMMARIZE (
    'inspections',
    'inspections'[Fleet],
    'inspections'[Position],
    "last date", MAX ( 'inspections'[Date] ),
    "Value",
    VAR position = [Position]
    RETURN
        CALCULATE (
            SUM ( inspections[Value] ),
            FILTER (
                ALL ( inspections ),
                inspections[Position] = position
                    && inspections[Date]
                        = CALCULATE (
                            MAX ( inspections[Date] ),
                            FILTER ( ALL ( inspections ), inspections[Position] = position )
                        )
            )
        )
)

Three measures to calculate conditional totals:

Total Unique Positions -Over (10) Qty = 
CALCULATE ( DISTINCTCOUNT ( 'Table'[Value] ), 'Table'[Value] > 10 )

Total Unique Positions - Between (7-10) Qty = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Value] ),
    'Table'[Value] >= 7
        && 'Table'[Value] <= 10
)

Total Unique Positions - Below (7) Qty = 
CALCULATE ( DISTINCTCOUNT ( 'Table'[Value] ), 'Table'[Value] < 7 )

Sample file is attached that hopes to help you, please check and try it: Calculate the distinct positions considering the last inspection in the month the vehicle.pbix 

Best Regards,
Yingjie Li

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

[Ashish_Mathur]

Hi,

How have you got those answers - 1 for each?  Shouldn't the answer of Total Unique Positions - Over (10) Qty be 2?

[alexandrem79]

Thanks for your answer!

I need the value of the most recent inspection of the month:

Fleet	Position	Date	Value
Fleet1	695475-XC	2020-04-05	6.8
Fleet1	695475-XC	2020-04-16	10.2

Fleet	Total Unique Positions - Over (10) Qty	Total Unique Positions -  Between (7-10) Qty	Total Unique Positions -  Below (7) Qty
Fleet1	1	0	0

I tried that, but it did not work:

Total Unique Positions - Over (10) Qty =

CALCULATE(

DISTINCTCOUNT(inspections[Position]),

FILTER(inspections, inspections[Value] > 10),

FILTER(inspections, inspections[Date] = MAX(inspections[Date]))

)

It's calculating the last date of the table and ignoring the position of the vehicle.

[amitchandak]

@alexandrem79 , Please find the attached solution file. You may have to change no of groupbys in summarize as per need.

The measure is the latest. and measure 2 is the latest in month

[Ashish_Mathur]

Hi,

The most recent inspection for the month is 16 April 2020 for 695475-XC and 12 April 2020 for 324567-KQ.  The value for both such instances is > 10 so thae answer should be 2.  You may download my PBI file from here.

Hope this helps.