Calculate current month YTD minus previous month YTD

[bhaveshp90]

Hello,

I have the table as shown below with YTD and MTD as expressions.

MTD = (Current month YTD) - (Previous month YTD).

My expression for MTD = YTD - Alt(Above(Total(YTD)), 0).

This expression is calculating correctly when selecting individual project number, but for all the project numbers selected It is calculating on per row basis which is completely wrong as shown in the below image;

Any idea on how to avoid it? (I want to calculate MTD as current month YTD - previous month YTD per Project_Number)

attached are files for your reference;

thanks

[sunny_talwar]

Once again, I don't really know what the issue is? I see repeating numbers for YTD and MTD.... but that has to do with the YTD expression and not MTD... because MTD is just using YTD... if YTD is not right, then you need to start a new discussion to talk about YTD... because MTD seems to be doing what you asked for... unless I am missing something here?

[bhaveshp90]

stalwar1‌, the YTD problem is because of the old qvd file I'm loading before.

Now my YTD values are correct, but my MTD is still same as YTD; I have used your expression;

Can you tell me what is wrong in MTD here?

thanks a lot

[sunny_talwar]

Two issues

1) I don't think you are using the most recent expression I gave in the response you have marked as correct

2) You are not making selection in Month field so, you will have to ignore selection in the Month field as well in addition to MonthYear and Snapshot_Date.

If you fix the above two things, your expression should work.

[bhaveshp90]

I am using the same expression now, and filtering based on Month Year, but not able to get the desired output

[sunny_talwar]

Can you attach the new version

[bhaveshp90]

Sure, please find the files in the attachments

[sunny_talwar]

Here try this....

Aggr(If(Only({1} Project_Number) = Above(TOTAL Only({1} Project_Number)),

If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)',

0,

Sum({<Cost_Code = {'3'}>}Updated_OEC_Budget)

-Sum({<Cost_Code = {'3'}>}Forecast_Cost_to_Complete)

-If(IsNull(Variance_YTD),

0,

Prior_Updated_OEC_Budget - Prior_Forecast_Cost_to_Complete))

-

Alt(Above(TOTAL

If(Only({<MonthYear, Snapshot_Date>}Num_Month_Project_Cost) = '$(vPreYearLastMonth)' and Only({<MonthYear, Snapshot_Date>}Year) = '$(vPreYear)',

0,

Sum({<Cost_Code = {'3'}, MonthYear, Snapshot_Date>}Updated_OEC_Budget)

-Sum({<Cost_Code = {'3'}, MonthYear, Snapshot_Date>}Forecast_Cost_to_Complete)

-If(IsNull(Only({<MonthYear, Snapshot_Date>}Variance_YTD)),

0,

Only({<MonthYear, Snapshot_Date>}Prior_Updated_OEC_Budget - Prior_Forecast_Cost_to_Complete)))

),0),

If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)',

0,

Sum({<Cost_Code = {'3'}>}Updated_OEC_Budget)

-Sum({<Cost_Code = {'3'}>}Forecast_Cost_to_Complete)

-If(IsNull(Variance_YTD),

0,

Prior_Updated_OEC_Budget - Prior_Forecast_Cost_to_Complete))

), Project_Number, MonthYear)

Had to clear up your expression for all those unnecessary Num() which were making your expression unreadable. Select in MonthYear field and Not Month field

[bhaveshp90]

thank you stalwar1‌. This is what I am looking for exactly

[bhaveshp90]

Can we not make 2016 and 2017 year selection here?

[sunny_talwar]

What happens when you select 2016 or 2017?