Forum Discussion
Setup PLAN values based on values from 1st month
Dear community,
As I am new with PBI I am currently struggling over a simple issue which I can not solve on my own. I should appreciate your support.
Here we go: I have several reports which I load into Dataset. Each report showing data of a specific month (Date). What I am trying to do is to create measures to setup PLAN values based on the values from 1st month.
1) Reduction target per month based on 1st report values (values will be equaly divided per month, so the reduction target per month is equal each month)
2) commulated reduction
3) cummulated target backlog in stock
In my example I have created a short table showing "Items in stock" values per country, per month. Target is to bring the "Items in stock" to zero by the end of the year. So the Plan should use the inital values from 1st month and divide it by 12 to be able to compare the plan per month by actuals per month.
In my Dashboard I want to show current values per month as well as PLAN redurction target in each month. So the Visual shows the report data in the ROW for each month...
Do you have an idea how to solve my litte problem? I really should appreciate your tips.
Example Table
Hi Anonymous
I took a swing at this...
See the attached pbix for the measures. I'm interpreting what you said as the following:
"commulated reduction" means the accumulated actual reduction in items in stock from one month to the next."cummulated target backlog in stock" means the difference between the cumulative actual reduction in a month vs. the targeted reduction for that month.
Hope this helps!
5 Replies
Hi Anonymous
I took a swing at this...
See the attached pbix for the measures. I'm interpreting what you said as the following:
"commulated reduction" means the accumulated actual reduction in items in stock from one month to the next."cummulated target backlog in stock" means the difference between the cumulative actual reduction in a month vs. the targeted reduction for that month.
Hope this helps!
Anonymous de nada...glad I could help! 🙂
Hi Anonymous
I hope I've correctly understood your requirements
Here is a sample file with the solutionhttps://www.dropbox.com/t/g4NNGFHCafoMBwDc
If you don't have a date table then you need to create a simple one minimum with the following columns (let me know if you need any help creating it)
Create the relationship between the two tables
This is how the report looks like
I believe by "Accumulated Reduction" you mean "Year To Date (YTD)" as this value shall restart annually.
The measures areStart Stock = SUM ( Stock[Items in Stock] )End Stock = VAR CurrentYearMonthRank = MAX ( 'Date'[Year Month Rank] ) VAR Result = CALCULATE ( [Start Stock], REMOVEFILTERS ( 'Date' ), 'Date'[Year Month Rank] = CurrentYearMonthRank + 1 ) RETURN IF ( NOT ISBLANK ( MIN ('Date'[Date] ) ), Result )Actual Reduction = VAR StartStock = [Start Stock] VAR EndStock = [End Stock] RETURN IF ( NOT ISBLANK ( EndStock ), StartStock - EndStock )Planned Reduction = VAR CurrentYear = MAX ('Date'[Year] ) VAR FirstMonthStock = CALCULATE ( [Start Stock], REMOVEFILTERS ( 'Date' ), 'Date'[Month] = 1, 'Date'[Year] = CurrentYear ) VAR Result = DIVIDE ( FirstMonthStock, 12 ) RETURN ResultActual Reduction = VAR StartStock = [Start Stock] VAR EndStock = [End Stock] RETURN IF ( NOT ISBLANK ( EndStock ), StartStock - EndStock )Have a great day!
