Forum Discussion

ziyabikram96's avatar
ziyabikram96
Icon for Helper V rankHelper V
5 years ago
Solved

Measure involving Second Last Date

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
Need Help
Show and Tell

15 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      ziyabikram96
      Icon for Helper V rankHelper V
      5 years ago

      Hi,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      ziyabikram96
      Icon for Helper V rankHelper V
      5 years ago

      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.