Measure involving Second Last Date

[ziyabikram96]

Hi,

I am trying to create a measure where the difference between a vehicle's today's Start Odometer Reading and Previous day's End Odometer reading. Now, the previous day can be 7th Jan or any other day when the vehicle was used. 

I am using this variable to get the previous date and then using it in another variable's filter.

MINX(TOPN(2,'Driver Daily Data(Whole)','Driver Daily Data(Whole)'[Date]),'Driver Daily Data(Whole)'[Date])

Minx for the previous day and Maxx for the present day.

The problem is the date context. When I add dates in the Matrix column or choose a date from the filter, measure gets filtered down to that specific date. I have also tried other measures too but to no avail.

How can I solve it?

Thanks

[V-pazhen-msft]

@ziyabikram96 

Try the measure, below, I have take out the variable as measure to show the result.

Difference = 
var todaystart = CALCULATE(MAX([Vehicle startOdo]),FILTER(ALL('Table'),[Date]=MAXX(ALL('Table'),[Date])))
var previousend= CALCULATE(MAX([Vehicle endOdo]),FILTER(ALL('Table'),[Date]=CALCULATE(MAX([Date]),FILTER(ALL('Table'),[Date]<CALCULATE(MAX([Date]),ALL('Table'))))))

Return todaystart-previousend

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[ziyabikram96]

You can access the dataset using this link.

What I want is:

28th Dec's Start Odo - 24th Dec's End Odo

24th Dec's Start Odo - 22nd Dec's End Odo

Today's Start Odo - Previous Reading Day's End Odo

I would add dates in matrix column, Vehicle in the rows and the value would contains the Difference Value.

https://pentagonfoodgroup-my.sharepoint.com/:x:/g/personal/ziyab_ikram_pentagonfoodgroup_co_uk/EbJxA... 

[Ashish_Mathur]

Hi,

Why should it be 24th Dec's Start Odo - 22nd Dec's End Odo when there is data for 23rd Dec?  Should it not be 24th Dec's Start Odo - 23rd Dec's End Odo?  Also, for the same registration, ow can there there be 2 rows for the same Date?  Please clarify.

[ziyabikram96]

Hi,

I missed 23rd Dec so yeah, 24th Dec StartOdo - 23rd Dec EndOdo.

The challange is 28th Dec StartOdo - 24th Dec EndOdo.

A vehicle can be driven twice in a day but it happens occasionally. So, you can just assume a single 23rd Dec.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[ziyabikram96]

Hi,

It does seem like the correct solution but I can't download it. It gets downloaded as a bunch of files instead of a single pbix file.

[Ashish_Mathur]

Hi,

Please try again.  It downloads just fine.

[ziyabikram96]

It is working. Thanks a lot @Ashish_Mathur  

[Ashish_Mathur]

You are welcome.

[CNENFRNL]

@ziyabikram96 , you might want to try following measures,

Current Day = MAX ( 'Driver Daily Data(Whole)'[Date] )

Previous Day = PREVIOUSDAY( 'Driver Daily Data(Whole)'[Date] )

As to "Second Last Date", here's a more verbose way in line with your measure,

Second Last Date = 
VAR __dd = [Current Day]
RETURN
    MINX (
        TOPN (
            2,
            CALCULATETABLE (
                'Driver Daily Data(Whole)',
                'Driver Daily Data(Whole)'[Date] <= __dd
            ),
            'Driver Daily Data(Whole)'[Date]
        ),
        'Driver Daily Data(Whole)'[Date]
    )

[ziyabikram96]

Hi,

Second Last Date doesn't work when dates are added in matrix column and vehicles in the rows. Date context renders it ineffective. 

[Greg_Deckler]

@ziyabikram96 - You will need to incorporate an ALL into your filter context so that you effectively override the filter from the slicer in your measure.

[ziyabikram96]

When I select 8th Jan from the filter or have in the matrix column, I want it to take the Start Odo of 8th Jan and End Odo of 7th Jan. Instead it takes the End Odo of 8th Jan.

[ziyabikram96]

I have also used that before but didn't work.