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
Hey guys,
Here's a tricky one. I'm trying to create a projection that takes the current year sold qty so far this year, and using last year's week over week % change, project how many sold units we will well for the remainder of the year. For the first week into the future I would take last week's sold qty and multiple it by (1 + % change) for the same week last year. For 2 weeks+ into the future I would then need to look at the previous week's projected sold qty and multiply that by (1 + % change) for the same week last year.
Attached is how the data would look like in excel and the Projected Qty column is the calculation I am trying to replicate in Power BI. The excel formula for that column is =IF(ISBLANK(D21), E21*(1+C22), D21*(1+C22))
Hi @anthonyloh
Kindly make the table as copyable will be easier for me to repro the data.
It seems similar to the case I solved yesterday, check it out here:
Also here're some other threads could be your reference:
I created a dummy sample as below, kindly check the results: (Pbix attached)
Column = IF([SoldQty]<>BLANK(),MAXX(FILTER('Table',[Index]=EARLIER('Table'[Index])-1),[SoldQty])*(1+[%change]),MAXX(FILTER('Table',[Index]=EARLIER('Table'[Index])-1),[ProjectedQty])*(1+[%change]))
@v-diye-msft Here is the data:
Calendar Week Key | Sold Qty LY | % Change WOW | Sold Qty TY | Projected Qty |
202005 | 24 | 141 | ||
202006 | 473 | 18.7083333 | 1,508 | |
202007 | 512 | 0.0824524 | 1,495 | |
202008 | 642 | 0.2539063 | 1,347 | |
202009 | 763 | 0.1884735 | 1,564 | |
202010 | 1,419 | 0.8597641 | 1,509 | |
202011 | 1,983 | 0.3974630 | 1,316 | |
202012 | 1,836 | -0.0741301 | 671 | |
202013 | 1,775 | -0.0332244 | 1,030 | |
202014 | 1,760 | -0.0084507 | 1,139 | |
202015 | 1,865 | 0.0596591 | 1,057 | |
202016 | 2,132 | 0.1431635 | 2,505 | |
202017 | 2,173 | 0.0192308 | 2,046 | |
202018 | 2,205 | 0.0147262 | 1,363 | |
202019 | 2,163 | -0.0190476 | 2,693 | |
202020 | 1,937 | -0.1044845 | 2,230 | |
202021 | 2,065 | 0.0660816 | 3,217 | |
202022 | 2,274 | 0.1012107 | 2,399 | |
202023 | 2,008 | -0.1169745 | 1,923 | |
202024 | 2,458 | 0.2241036 | 1,957 | |
202025 | 3,822 | 0.5549227 | 3,043 | |
202026 | 2,574 | -0.3265306 | 2,049 | |
202027 | 5,621 | 1.1837607 | 4,475 | |
202028 | 3,639 | -0.3526063 | 2,897 | |
202029 | 4,089 | 0.1236603 | 3,256 | |
202030 | 3,677 | -0.1007581 | 2,928 | |
202031 | 3,821 | 0.0391624 | 3,042 | |
202032 | 3,126 | -0.1818896 | 2,489 | |
202033 | 3,586 | 0.1471529 | 2,855 | |
202034 | 2,740 | -0.2359175 | 2,182 | |
202035 | 2,439 | -0.1098540 | 1,942 | |
202036 | 1,809 | -0.2583026 | 1,440 | |
202037 | 1,735 | -0.0409066 | 1,381 |
I looked at your calculation and I think you misunderstood which column I am calculating. I am trying to calculate the Projected Qty column in Power BI and in the data above example I am simply showing what the expected result should look like. In your data model you have Projected Qty as manually entered data and then you're calculating something else.
So if we are currently in week 202025 the calculation for projected qty would be 1957 (last week of actual sales) * (1 + 0.555) (the change % for week 202025. For week 202026 and onward, the projected qty would be 3043 (last week's projected qty) * (1+ -0.327) (the change % for week 202026.
Thank you,
Anthony
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |