Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Help with Correct Sum for Most Recent and last Date



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:


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.



Frequent Visitor

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

I was able to solve thgs. Thanks!

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors