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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.