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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.