Forum Discussion
Helper IEstimation from past month
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!
4 Replies
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 Anonymous,
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:
Thanks!
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
