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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JHOLT
New Member

return a filtered value according to month

I want to calculate days of stock based on actual monthly stock data and monthly forecast consumption.

I have calculated this as a running total, where days of stock = number of days for Forecast to be greater than current stock.

Each month there is a new "version" of the forecast data added in the same Database. Currently my model allows me to filter the 'version' of the forecast, and the days of stock updates accordingly

 

I want to be able to fix the 'version' of the forecast to specific months, so that historical data does not change

 

e.g. 

 

if Month = Jan, calculate days of stock considering forecast version 6th Feb, 

If month = Feb, calculate days of stock considering forecast version 29th Feb,

if month = March, calculate days of stock considering 26th March forecast, and so on

 

There is not a set rule of +1 month for the forecast, it is simply the version closest to the closure of the month, so I need to be able to define the specific filtered value for each month of stock

 

 

3 REPLIES 3
Greg_Deckler
Super User
Super User

Maybe Days of Supply? https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler,

 

It is in essence the same calculation, I was able to make this work in a different way (e.g. after how many days will my forecast be greater than my inventory)

 

The problem I have is that each month there is a new version of the forecast, and I don't want previous months to update with the new data

 

I can change the version of the forecast as a filter, but this will change all months. Instead, i want to 'pin' a version of the forecast to each specific month. I hope this clarifies

Can you share sample data and sample output.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.