Estimation from past month

[as3znaus]

Hi everyone!

Hope someone can help me with this. 

I have the following data in an excel spreadsheet.

Code

Direct costs

Staff  costs

Billing

Date

Report Date

P14.0001	0	-4.080	0	oct.-16	sep-16
P14.0001	0	-510	0	nov.-16	sep-16
P14.0001	0	-510	0	dic.-16	sept-16

As you can see, I have a Staff cost of 4.080 for October on my September Report (this number is an estimation).

The next month I will have the real cost for October, so I need a DAX function to compare these values.

1_Estimation M-1 for month M 

I need a formula to find the number that corresponds to date M (oct-16) and report date M-1 (sep-16), in this case 4.080.

2_Actual cost

I already got this meassure.

3_Difference between value

I already got this meassure.

Thanks a bunch!

[v-shex-msft]

Hi @as3znaus,

You can try to use belwo formula to get previous month's value:

Total cost of Previous Month=
var currCode= LASTNOBLANK(Table[Code],[Code])

Return
IF(Month(MAX[Date]))>1,

SUMX(FILTER(ALL(Table),[Code]= currCode && Month([Date])=Month(MAX([Date]))-1),[Staff  costs]),

SUMX(FILTER(ALL(Table),[Code]= currCode && Year([Date])=Year(MAX([Date]))-1&&Month([Date])=12),[Staff  costs]))

Regards,

Xiaoxin Sheng

[as3znaus]

Hi @v-shex-msft,

I have tried your formula but I can't make it work...these are my actual column names.

Code = Código

Table = DatosCostes

StaffCosts = CostePersonal

Date=Fecha

ReportDate= Fecha Report

I changed them and I get the following error:

http://jmp.sh/79eEjfB

 Thanks!

[v-shex-msft]

Hi @as3znaus,

It seems like you have miss the "=" charater.(my formula is a measure.)

In addition, you can try to use english character to name the variable.

Regards,

Xiaoxin Sheng

[as3znaus]

Hi @v-shex-msft,

I finally solved it in a different way, I used a colum of "Current Date".

Thanks anyway!