Calculate the difference between 2 measures from different rows

Question

I´m new in DAX and power pivot, and I have a small problem that i can´t solve. I hope you can help me.

I keep a record of my data per week, so I generate the system sales report every Friday night. Every month I establish in another table my month goals divided by week so I can make a comparison of the achievements whit my goal each and every week.

The problem is that I want to create a measure on my client pivot table, that measures the difference between the client goal for the current week minus the actual clients reported on the last Friday report (last week).

Here is an example of my pivot table.

enter image description here

Here is an example of my Dax measure:

=CALCULATE(sum(MetaTotalMes[#Clientes]),
FILTER(MetaTotalMes,MetaTotalMes[#Sem])) - CALCULATE(sum(Cartera[Clientes]),
FILTER(Cartera,[# Sem.]-1))

I´m using calculate and filter to get the sum of clients for each week, the thing is that I need to subtract the actual # of clients of the past week to get the actual goal from the week.

Here is an example using excel, to show what I need to do with the dax measure.

UDATE

I made some adjustments to the function @AlexisOlson gave me and is working as I needed with a small problem, the measure stops returning values once the values on the column Actual client are BLANK, so to fix the problem I want to use LASTNONBLANK function, in order to copy the last registered value in all the BLANK fields; Can you please help me? Here are the changes I made to the original function you gave me.

=VAR CurrentWeek = MAX ( Cartera[# Sem.] ) 
RETURN 
SUM(MetaTotalMes[#Clientes]) - CALCULATE(SUM(Cartera[Clientes]),
FILTER(ALL(Cartera),Cartera[#Sem.]=CurrentWeek-1))

Here is the function I´m using to calculate de ACTUAL CLIENTS column (here is where I´m trying to include the LASTNONBLANK function).

=CALCULATE(SUM([Clientes]),FILTER(ALL(Cartera[# Sem.]),
Cartera[#Sem.]=MAX(Cartera[# Sem.])))

Here is an example of the expected calculus, I use colors to be more specific on the needed calculus. The yellow fields are the BLANK fields, the one that I´m trying to fill, whit the last registered value of the column.

https://i.stack.imgur.com/Mnfgr.png

Alexis Olson Alexis Olson · Accepted Answer · 2019-07-08T16:24:18

I think you want something like this

Difference =
VAR CurrentWeek = MAX ( Cartera[# Sem.] )
RETURN
    CALCULATE ( SUM ( Cartera[Clientes] ), Cartera[# Sem.] = CurrentWeek + 1 )
        - SUM ( MetaTotalMes[#Clientes] )

The CALCULATE allows you to replace the current week filter context with the following week.

I don't think you need a CALCULATE for the 2nd part since it's calculated within the context of the current week if your table relationships are set up correctly.

Calculate the difference between 2 measures from different rows

1 Answers