Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all
i am working on some data where i have been using multiple measures to work out a forecast of sorts
the forecast value should be in column "Measure 5" which should be "sum of sales in previous month" + "sum of sales in the same period last year" - "sum of sales in the same period last year a month prior"
eg. measure 5 for "2023-08" should be Sum of 2023-07 sales + sum of 2022-08 sales - sum of 2022-07 sales
the issue i come across is that i need to populate the Column "Measure 6" with the calculated value from "Measure 5"
eg. value for "Measure 6" 2023-09 needs to be the calculated value for "Measure 5" 2023-08 which is "237,918.59"
so far "Measure 6" is if Sales column is blank then calculate previous month total sales, i tried adding the value for "Measure 5" into the next row, but this creates a circular reference as "Measure 5" references "Measure 6"
"sales LY" is sum sales from previous year and "measure 4" is sum sales last year minus a month which is working as expected
Solved! Go to Solution.
Hi,
Please find attached the PBI file.
Hope this helps.
Hi,
I can try. Share the download link of the file and show the expected result of Measure5 and Measure6 clearly for a few rows.
Hi Ashish
the below table is the expected results, and i have also linked a sample data for the pbix file
Year-Month | Sales | Measure 6 | Sales LY | Measure 4 | Measure 5 |
Jan-22 | 244283.168 | ||||
Feb-22 | 224393.764 | ||||
Mar-22 | 241754.498 | ||||
Apr-22 | 225907.883 | ||||
May-22 | 238466.849 | ||||
Jun-22 | 232921.831 | ||||
Jul-22 | 230250.714 | ||||
Aug-22 | 244639.15 | ||||
Sep-22 | 235385.347 | ||||
Oct-22 | 236243.386 | ||||
Nov-22 | 238495.042 | ||||
Dec-22 | 238328.802 | ||||
Jan-23 | 236112.364 | 244283.168 | |||
Feb-23 | 217532.405 | 224393.764 | 244283.168 | ||
Mar-23 | 250435.421 | 241754.498 | 224393.764 | ||
Apr-23 | 229995.665 | 225907.883 | 241754.498 | ||
May-23 | 255642.994 | 238466.849 | 225907.883 | ||
Jun-23 | 246647.513 | 232921.831 | 238466.849 | ||
Jul-23 | 229635.974 | 230250.714 | 232921.831 | ||
Aug-23 | 229635.97 | 244639.15 | 230250.714 | 244024.41 | |
Sep-23 | 244024.41 | 235385.347 | 244639.15 | 234770.61 | |
Oct-23 | 234770.61 | 236243.386 | 235385.347 | 235628.65 | |
Nov-23 | 235628.65 | 238495.042 | 236243.386 | 237880.30 | |
Dec-23 | 237880.30 | 238328.802 | 238495.042 | 237714.06 | |
Jan-24 | 237714.06 | 236112.364 | 238328.802 | 235497.62 |
https://drive.google.com/file/d/1wJTG0qC5Pesjn1X44NtcVsEK6Ie8galA/view?usp=sharing
Hi,
I get an access denied message. I'd request you to share the download link of an Excel workbook. In that workbook, ensure that you write formulas to get your desired result. I will try to translate those formulas in the DAX language.
Hi Ashish
sorry about that, i have updated the share premissions on the link, you should be able to acccess the PBIX now
below is a link to a excel sheet of what i would like to do in power bi, with excel formulas
thanks
Thanks Ashish
i will try and breakdown your solution and apply it to my pbix
You are welcome.
User | Count |
---|---|
90 | |
85 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |