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
ConfusedAnalyst
Frequent Visitor

Adjusting the Starting Column for a Calculation based on the Latest Date for the specific Row

I'm creating a running inventory tracker for our company and I need the calculation to start only on the date that the inventory was updated for the material in question. 

ConfusedAnalyst_0-1673279574333.png


As you can see in the screenshot above they are all starting their calculations on the week of the 24th. This may not be accurate as some may have had their inventory counts updated before or after this date, This would mean I would only want to start the calculation on the date the inventory count was conducted and leave the preceding cells blank.

Secondarily, I would like to display the OnHand quantity as a static column after the Material and Description columns separate from the Date columns, is this possible? Here's a link to my data. 

https://www.dropbox.com/s/t1ydza4rz107bfp/SM%20Updated%20Test.pbix?dl=0

Thank you!

1 ACCEPTED SOLUTION
ConfusedAnalyst
Frequent Visitor

Unfortunately this issue was fixed by forcing a static starting date. It was very hard to trick powerBI into starting with blank data if data exists. It would have had to have been hardcoded per material which is not possible imo. I appreciate you trying to help Felix. 

View solution in original post

2 REPLIES 2
ConfusedAnalyst
Frequent Visitor

Unfortunately this issue was fixed by forcing a static starting date. It was very hard to trick powerBI into starting with blank data if data exists. It would have had to have been hardcoded per material which is not possible imo. I appreciate you trying to help Felix. 

MFelix
Super User
Super User

Hi @ConfusedAnalyst ,

 

Your issue is related with the calendar table, since you are forcing the beginning date to be for the Maximum value for a specific product you will get only the data after the start o f the calendar:

MFelix_0-1673910525609.png

Since your calendar does not have dates before this you will not get expected result try this instead:

MFelix_1-1673910587544.png

 

 

Concerning the onhand if you want this value to be updated with the last one create the following column on the HQMT table:

ONHAND TOTAL = 
MAXX(TOPN(1,FILTER(ALL(INMT), INMT[Material] = HQMT[Material]), INMT[LastCntDate], DESC), INMT[OnHand])

 

Now you can use has a separete column

MFelix_2-1673911658160.png

 

If this does not suit your needs can you please share a little bit more insight on the calculations you need.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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