Hello!
I am facing a challenge for creating in PowerBI what I previously developed in Excel. Below is the explanation of my tables, the logic to create with DAX and the result I would need to have.
I have also attached a sample file with easy data : (here because it looks like I can't upload file in the forum) : https://we.tl/t-I1sZtrqCOW
If anyone has clues to help me, it would be amazing.
Tables :
#1 Monthly Target : for every year/month what is the target net tons to produce by category of items
#2 Gross Net Rate convert : for every category of items and year/month : what is the rate to apply on gross tons production to get net tons.
#3 Line Orga : for every year/month how many days per week each production line will run
#4 Production (realized) : the net tons already produced for each items by day
#5 Production (planned until W+3) : it’s from a file with the gross tons planned to produce between this week (included) until W+3. By date and item.
#6 Production (weekly global plan) : it’s the global production planning for each item by production line at weekly level (not by day)
Logic & Required result :
We need to have a KPI : projection of production realized at the end of the month.
This KPI is only to be read once per week (on Monday when you receive the weekly file source for table #5)
In result, I would have a table with following columns: (the table would give my end of month production projection as per the month of the Monday date). So if look at this KPI Monday 29 April, it’s still projection of April Month.
On the BI report page, maybe on the top, we need to specify year and month we are looking at.
And this week’s Monday date.
Then table :
#column1: Category : it will just tell me items category (in my attached example I would have CAT1 & CAT2 as values) + Line TOTAL (not taking into consideration Categories)
#c2: Net Target : for each category the monthly target (net tons from table #1)
#c3: realized production : for each category what we already produced from source table #4 (from beginning (first day) of the month we look at until the Sunday before the Monday of KPI calculation (included). In my example of running kpi Monday 29 April = it would be production from April 1 to Sunday 28 April included)
#c4: KPI1 in % called Attainment1 = Simple calculated column by dividing c3 by c2. With my data as example it would give CAT1 = 150/200 (75%) and CAT2 = 80/100 (80%)
#c5: the production to come from today to end of the month (this is where DAX becomes complicated) :
Today in Excel I apply formulas : I take max production date from source table #5 and if this date is superior or equal to last day of current month then “NO” as no W+4 would be to be taken into account in current end of month projection. Otherwise “YES”.
In any case, I also identify for W+4 its ISO week number.
Scenario1 : Easy one when “NO” (example of KPi looked at Monday 29 April) my table 5 = Production (planned until W+3) would include production planned from 29 April (Week now) until Sunday 26 May (W+3)
So for this column we just need to take from table 5 the planned production for every day included in current month (in my example April 2024 : 25 + 25 tons for CAT1 on 30 April + 29 April)
This is gross tons : we need to multiply the figure by the rate from table #2 (the rate depends of Category and year/month) => and we finally have production to come in net tons.
Scenario 2: Difficult one. When “YES”, it would be the case for example for January 2024 kpi, if we looked at this report on Monday 1 January 2024, would would production realized (nothing yet in this case) + planned production from table 5 (from Monday 1 January until W+3 included so Sunday 28 January) But we still miss 3 days until end of month. That’s the special case where we also need to get planned data further than W+3 from another table : table 6.
As table 6 is planned production at weekly level (not daily like table 5) we would need to take global production planned for each production line in week 5 of 2024 that we would divide by the line organization (table 3 : Line Orga). For example if 2024 week 5 (considered as month = January 2024) has orga = 7 days for Line L1 then production planned for L1 in W5 would be divided by 7.
As we have 3 days belonging to January 2024 in W5, in final we multiply the figure by 3. We gather the results then, by Category. And we again apply gross/net rate by category (because prod planned in table 6 is also gross tons).
#c6 : KPI2 in % called Attainment2 = Simple calculated column by dividing (c5+c3) by c2.
#c7 : Simple calculated column = (c5+c3) minus c2
#c8 : Total projected = c3+c5
