Help with Correct Sum for Most Recent and last Date

[ksingh11]

Hello,

I am trying to get the right values on sum for current and previous models.

We are having a table with Model as column that we update on monthly basis and call the Model by its month name. So, current model is 202304 for example, if we updated in May and we also have previously set models 202303, 202302 and so on for several months.

I am trying to now separate some of the values from current model in way that all the FC_Vol represent numbers from current model 202304 (Values starting from 202305 - 202504 from May to next 24 months) and also, would like to have 202303 Model's value for the month April 202304 only. So, going forward all values from current model and the current month should have value from previous model, leaving the remaining historical values from current Model for last March, Feb and so on...Just interested in changing April's value from last Model.

Looking for AC Vol for 202304 and remaining should be FC Vol

OR 202304 and prior to it can be AC Vol and 202305 to the rest in future to be FC Vol

So, AC Vol should be from Date 202304 for Model 202303 for first option

I just changed AC Vol to as follows in the model:

AC Vol =

VAR PreviousModel = MAX(Sheet1[Model]) - 1

VAR AC_Vol = CALCULATE(SUM(Sheet1[Vol1]), Sheet1[Model] = PreviousModel, Sheet1[Date] =  PreviousModel+1, filter(Sheet1, Sheet1[Month] = 1))

return AC_Vol

Also, I never had this issue before, but the sum also doesn't give right values in the example. Plus, variables are supposed to be seen in rows left side, but are clubbed with Date here.

Attaching the dummy model here:

https://drive.google.com/file/d/1TZ7CJwyuBeQchryTNxkM_27VFbW0IhRX/view?usp=drive_link

Correction - For the above dummy data, I seem to be getting right answer, but for my real data, for some reason, sums are all wrong ...any thoughts? I do have some NULL values in my real data. Can that be the cause? If so, how can we resolve that issue?

Also, it would be nice to know DAX if we want to see historical values on AC ie. 202304 and prior to it can be AC Vol from all previous Models and 202305 to the rest in future to be FC Vol

Any help to solve this issue will be helpful.

Thanks!

[ksingh11]

I think in my real data, for some reason, its adding several numbers. I am assuming, its adding values from various months 1 through some months, not through 12th month.. Not sure where the sum is coming wrong

[ksingh11]

I was able to solve thgs. Thanks!