How to get prior version data in a measure

[sjahan133]

hi all

seeking some help please. i run a powerbi report that takes in demand forecast in a lot of detail, but ultimately the report builds up a profile of demand forecast by month. See below where the columns represent the published forecast month, and the rows represent the demand months

now whilst this measure works, as you can see when we start going close to the end of the calendar year numbers start disappearing. this is because the raw data is rolling each month so the historical month column drops off as a new month comes on. 

i'm trying to create a measure that looks at the latest forecast version where there is data (i.e. in the above case, November's data) but where there is data missing look at the prior version's info. struggling with the dax code here. below was my first attempt but as you can see it doesnt yield the right output (as i believe i'm filtering the data with the TRUE operator)

below is what the data table looks like. each new forecast version gets appended to the dataset.

any help here would be appreciated. 

[Anonymous]

Hi @sjahan133 ,

Could you pick one place to give an example of what your expected output is?
Check out these blogs to see if they are useful:
Optimizing LASTNONBLANK and LASTNONBLANKVALUE calculations - SQLBI
Alternative use of FIRSTNONBLANK and LASTNONBLANK - SQLBI

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly--How to provide sample data in the Power BI Forum--China Power BI User Group

[sjahan133]

Hi @Anonymous 

Thanks for the response and links, I'll be sure to check it out. 

So in my 2nd image  the measure is ECL_Current fcst qty. In the November 2023 column group on the same image you can see the measure equals to the Fcst qty measure for each of the table rows. This is expected.

However for the ECL_Current fcst qty measure for the row May22, the measure returns a blank given the Fcst qty measure doesn't actually have values for May22 in the November 2023 version. I wanted the ECL_Current fcst qty  measure to show for May22 the value 183,495 which sits in the Fcst qty measure in the October 2023 column group or forecast version