calculate daily change from previous day

[sridharpolina]

I have a summarized table with contains multple columns like product group and customer as well as as of date column. Associated with the as of table column is a flow date column which has the 'n' number of days in a given month for each as of date. I need to calculate the daily aggregate value of the Actual column and show the daily change from the prevoius day. I wrote a DAX query but it does not work. Any help is appreciated. Dax formula is below and the smaple data is attached.

[v-alq-msft · Accepted solution]

Hi, @sridharpolina 

You may try the following calculated column to see if it helps.

Daily Change_New =
VAR CurrentDate = ActualsDaily[as_of_date]
VAR PreDate =
    CALCULATE (
        MAX ( ActualsDaily[as_of_date] ),
        FILTER (
            ActualsDaily,
            ActualsDaily[customer] = EARLIER ( ActualsDaily[customer] )
                && ActualsDaily[facility_customer] = EARLIER ( ActualsDaily[facility_customer] )
                && ActualsDaily[as_of_date] < CurrentDate
        )
    )
VAR PreDue =
    CALCULATE (
        SUM ( ActualsDaily[Actual] ),
        FILTER (
            ActualsDaily,
            ActualsDaily[as_of_date] = PreDate
                && ActualsDaily[customer] = EARLIER ( ActualsDaily[customer] )
                && ActualsDaily[facility_customer] = EARLIER ( ActualsDaily[facility_customer] )
        )
    )
RETURN
    (
        IF ( PreDue <> BLANK (), ActualsDaily[Actual] - PreDue )
    )

Best Regards

Allan

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

[lbendlin]

What's your reasoning for using LOOKUPVALUE?  In your scenario CALCULATE would be much easier.

Daily Change_New = 
VAR CurrentDate = ActualsDaily[as_of_date]
VAR PreDate =
    CALCULATE (
        MAX ( ActualsDaily[as_of_date] ),
        ActualsDaily[as_of_date] < CurrentDate
    )
VAR PreDue =
    CALCULATE (
        SUM(ActualsDaily[Actual]),
        ActualsDaily[as_of_date] = PreDate
    )
RETURN
    (
        IF ( PreDue <> BLANK (), ActualsDaily[Actual] - PreDue )
    )

Adjust as needed for your other filters.