referencing a calculated measure value, a row above

[Jason_K]

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

[Ashish_Mathur]

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.

[Jason_K]

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 

[Ashish_Mathur]

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.

[Jason_K]

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

https://docs.google.com/spreadsheets/d/1GF52i0jm24iutbE-9Nm-nMHi-YSoowSY/edit?usp=sharing&ouid=10062... 

thanks 

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the PBI file.

Hope this helps.

[Jason_K]

Thanks Ashish

i will try and breakdown your solution and apply it to my pbix

[Ashish_Mathur]

You are welcome.