Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have a table with four columns :
ITEM / GOAL / PRODUCTION / DATE /
My date values are going from 01/01/2017 to 31/12/2017, there is a GOAL value for every line but the PRODUCTION column shows, of course, zero for the forthcoming days.
I would like to create a PRODUCTION TO DATE column that is empty if the DATEis bigger than today's date and a PROJECTION column wich will be the ratio between my PRODUCTION TO DATE and GOAL LINE.
For example :
ITEM | GOAL | PRODUCTION | DATE | PRODUCTION TO DATE | PROJECTION |
REP | 2 | 1 | 01/01/2017 | 1 | |
REP | 2 | 1 | 02/01/2017 | 1 | |
REP | 2 | 0 | 03/01/2017 | 1 |
If today is the 03/01/2017 i want my projection column to show 1 because my past PRODUCTION is half my GOAL.
Would you have something for me ? 🙂
Solved! Go to Solution.
Hi @Anonymous,
I really understand your requirement. Please create a calculated column using the following formula.
PROJECTION = IF(GoalTable[DATE]= CALCULATE(MAX(GoalTable[DATE]),ALLEXCEPT(GoalTable,GoalTable[CLIENT])),DIVIDE(CALCULATE(MAX(GoalTable[PRODUCTION]),ALLEXCEPT(GoalTable,GoalTable[CLIENT])),CALCULATE( COUNTAX(FILTER(GoalTable,GoalTable[DATE]<Today()),GoalTable[CLIENT]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))),BLANK())
Then I explain the formula step by step.
1. You need need to count the CLIENT where the date is littler than Today.
Column 2 = CALCULATE( COUNTAX(FILTER(GoalTable,GoalTable[DATE]<DATE(2017,3,1)),GoalTable[CLIENT]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))
2. Then you need to get the PRODUCTION you have did.
Column 3 = CALCULATE(MAX(GoalTable[PRODUCTION]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))
3. Then you can calculate the production rythm based on the two calcualte dcolumns. But you want to display the result in the last row for every CLIENT, so I use the IF function to contral the result. I test using 2017/3/1 , and get expected result as follows.
Please reproduce based on the steps using your real data. You can test and debug step by step.
Thanks,
Angelia
Hi @Anonymous,
>>I would like to create a PRODUCTION TO DATE column that is empty if the DATEis bigger than today's date
Please create a calculated column using the formula.
PRODUCTION TO DATE = IF(Table1[DATE]<TODAY(),1,BLANK())
>>a PROJECTION column wich will be the ratio between my PRODUCTION TO DATE and GOAL LINE.
It‘s confusing, how get 1? Please share more details for further analysis.
Thanks,
Angelia
>> I have a circular dependency detected... I think it's because my DATE is used on the request that creates my TABLE (it's a crossjoin table).
>> My projection is 1 because my until today PRODUCTION / GOAL ratio is 50% and my goal for tomorrow is 2 si I can predict that, if i'm producing at the same rythm, my production will be one 🙂
Anyone on this ?
Is my answer clear enough ?
Thanks 🙂
Hi @Anonymous,
You have resolved your issue by yourself? Please mark your workaround as answer.
Thanks,
Angelia
No 😞
I will try to be more precise so you may be able to help 🙂
I have a production table with the list of the items producted with a identification number, a date, and a few other caracteristics, this tables looks like this :
ID | CLIENT | PRODUCT | FACILITY | DATE |
438741452025 | 9906 | ETW | 682 | 01/01/2017 |
438741452033 | 9906 | ETW | 682 | 01/01/2017 |
318735834158 | 3425 | DEPOCW | 682 | 02/01/2017 |
318735833630 | 3425 | DEPOCW | 682 | 02/01/2017 |
And i have a goal table where i have for every day of the year, client, product and facility, a goal, it looks like this :
CLIENT | PRODUCT | FACILITY | DATE | GOAL |
9906 | ETW | 682 | 01/01/2017 | 1 |
9906 | ETW | 682 | 02/01/2017 | 1 |
9906 | ETW | 682 | 03/01/2017 | 1 |
3425 | DEPOCW | 682 | 01/01/2017 | 1 |
3425 | DEPOCW | 682 | 02/01/2017 | 1 |
3425 | DEPOCW | 682 | 03/01/2017 | 1 |
In this last table, i have created a production column that count the number of line in my production table, so the goal table now looks like this :
CLIENT | PRODUCT | FACILITY | DATE | GOAL | PRODUCTION |
9906 | ETW | 682 | 01/01/2017 | 1 | 2 |
9906 | ETW | 682 | 02/01/2017 | 1 | 0 |
9906 | ETW | 682 | 03/01/2017 | 1 | |
3425 | DEPOCW | 682 | 01/01/2017 | 1 | 0 |
3425 | DEPOCW | 682 | 02/01/2017 | 1 | 2 |
3425 | DEPOCW | 682 | 03/01/2017 | 1 |
What i would like to do is to create a projection column that would calculate for the forthcoming days, a projection of my production based on my past production rythm.
In this case, if we assume that we are today the 03/01/2017 i would like to be able to show in my 03/01/2017 lines the projection 1, because my goal from 01/01/2017 to 02/01/2017 was to produce 2 units, i have produced 2 units, so my production rythm is 100% of my goal. And because my goal today is 1 i can assume that i will produce 1 item. So the final table should look like this :
CLIENT | PRODUCT | FACILITY | DATE | GOAL | PRODUCTION | PROJECTION |
9906 | ETW | 682 | 01/01/2017 | 1 | 2 | |
9906 | ETW | 682 | 02/01/2017 | 1 | 0 | |
9906 | ETW | 682 | 03/01/2017 | 1 | 1 | |
3425 | DEPOCW | 682 | 01/01/2017 | 1 | 0 | |
3425 | DEPOCW | 682 | 02/01/2017 | 1 | 2 | |
3425 | DEPOCW | 682 | 03/01/2017 | 1 | 1 |
I hope it's clear enough now 🙂
Hi @Anonymous,
I really understand your requirement. Please create a calculated column using the following formula.
PROJECTION = IF(GoalTable[DATE]= CALCULATE(MAX(GoalTable[DATE]),ALLEXCEPT(GoalTable,GoalTable[CLIENT])),DIVIDE(CALCULATE(MAX(GoalTable[PRODUCTION]),ALLEXCEPT(GoalTable,GoalTable[CLIENT])),CALCULATE( COUNTAX(FILTER(GoalTable,GoalTable[DATE]<Today()),GoalTable[CLIENT]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))),BLANK())
Then I explain the formula step by step.
1. You need need to count the CLIENT where the date is littler than Today.
Column 2 = CALCULATE( COUNTAX(FILTER(GoalTable,GoalTable[DATE]<DATE(2017,3,1)),GoalTable[CLIENT]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))
2. Then you need to get the PRODUCTION you have did.
Column 3 = CALCULATE(MAX(GoalTable[PRODUCTION]),ALLEXCEPT(GoalTable,GoalTable[CLIENT]))
3. Then you can calculate the production rythm based on the two calcualte dcolumns. But you want to display the result in the last row for every CLIENT, so I use the IF function to contral the result. I test using 2017/3/1 , and get expected result as follows.
Please reproduce based on the steps using your real data. You can test and debug step by step.
Thanks,
Angelia
Can i also use the Facility and Product filters ?
I have a goal for each client - product - facillity combination 🙂
Hi @Anonymous,
You mean use Facility and Product in ALLEXCEPT(GoalTable,GoalTable[CLIENT]), right? Definitely you can use Facility and Product, which depands on your requirement. If you have resolved your problem, please mark the right reply as answer.
Thanks,
Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |