Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ksingh11
Frequent Visitor

Help with Correct Sum for Most Recent and last Date

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

 

ksingh11_1-1686180421106.png

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.

ksingh11_0-1686175951570.png

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!

2 REPLIES 2
ksingh11
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

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors