Getting previous value

[ansa_naz]

Hi friends

My data is in the following format:

Date                  Value

01/01/2020       100.00

01/01/2020       12.55

31/01/2020       50.00

02/02/2020       95.65

02/02/2020       1256.00

08/02/2020       62.55

I want to return the following in a matrix:

Current Date                Current Value              Previous Date             Previous Value

01/01/2020                   112.55                                                            

31/01/2020                   50.00                           01/01/2020                112.55

02/02/2020                   1351.65                       31/01/2020                50.00

08/02/2020                   62.55                           02/02/2020                1351.65                       

I have the following two measures:

Previous Date = 
VAR a =
    MAX ( 'Lift Contract History'[Date] )
VAR b =
    CALCULATE (
        MAX ( 'Lift Contract History'[Date] ),
        FILTER ( ALL('Lift Contract History'), 'Lift Contract History'[Date] < a )
    )
RETURN
    b

Previous Value =
CALCULATE (
    SUM ( 'Lift Contract History'[Value] ),
    FILTER (
        ALL ( 'Lift Contract History' ),
        'Lift Contract History'[Date] = [Previous Date]
    )
)

What I am getting is:

Current Date                Current Value              Previous Date             Previous Value

01/01/2020                   112.55                                                            

31/01/2020                   50.00                           01/01/2020                

02/02/2020                   1351.65                       31/01/2020               

08/02/2020                   62.55                           02/02/2020                      

Is there any way to obtain the previous value by amending my [Previous Value] measure? 

Many thanks for all help

[amitchandak]

@ansa_naz , Try a formula like this with date table

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[ansa_naz]

Hi @amitchandak so I think I have amended your measure correctly, see below, however I am still getting blank values for this measure:

Prev Annual Value =
CALCULATE (
    SUM ( 'Lift Contract History'[Value] ),
    FILTER (
        ALL ( Dates ),
        Dates[CalendarDate]
            = MAXX (
                FILTER ( ALL ( Dates ), Dates[CalendarDate] < MAX ( Dates[CalendarDate] ) ),
                Dates[CalendarDate]
            )
    )
)

I already had a date table, so I followed your logic but it doesnt work? Any further advice please?

[mahoneypat · Accepted solution]

Try this instead.  Doing your measure inside the Filter() is a different context.  Do it as a variable first.

Previous Value New = var prevdate = [Previous Date]

return CALCULATE (

SUM ( 'Lift Contract History'[Value] ),

FILTER (

ALL ( 'Lift Contract History' ),

'Lift Contract History'[Date] = prevdate

)

)

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[OneWithQuestion]

@mahoneypat 

I actually ran across this exact same issue and was also wondering why a variable has to be used to store the previous date?

Why can't the [Previous Date] be referenced directly inside the formula?

Why does it have to be this:

Previous Value New =
VAR prevdate = [Previous Date]
RETURN
    CALCULATE (
        SUM ( 'Lift Contract History'[Value] ),
        FILTER (
            ALL ( 'Lift Contract History' ),
            'Lift Contract History'[Date] = prevdate
        )
    )
​

Instead of this:

Previous Value New =
CALCULATE (
    SUM ( 'Lift Contract History'[Value] ),
    FILTER (
        ALL ( 'Lift Contract History' ),
        'Lift Contract History'[Date] = [Previous Date]
    )
)
​

Thank you for your help!

[mahoneypat]

By putting it in a variable, you make it a constant value that each row of the table being FILTERed is evaluated against.  When it is used inside the FILTER(), the measure is re-calculated on each row, using the Date value on that row.  Because no date can equal its previous date, no rows pass the Filter criterion.

I hope that explains it.

Regards,

Pat

[ansa_naz]

Many thanks @mahoneypat that works aces!! 

Would love to understand what you meant by "Doing your measure inside the Filter() is a different context.  Do it as a variable first.". I cant seem to understand why this made a difference