Help! How do I roll forward the latest value

[AKath_12]

Hi, 

I have a set of data carrying a value each year, by different locations; the value could be different, too. 

Since the value only comes in once per year on a specific month, however, I need this value to roll forward to the following months, until a new value comes in. 

Inactive relationship with the calendar table.

This is the current DAX formula I am using (Not sure where goes wrong), 

Latest Value =

VAR _LatestValue =

    CALCULATE(MAX('Result table'[Value]),

    USERELATIONSHIP('Calendar'[FYP_Key],'Result table'[Completed_MonthYear]),

    FILTER(ALL('Calendar'),'Calendar'[FYP_Key]<= MAX('Calendar'[FYP_Key])))

RETURN

if (SELECTEDVALUE('Result table'[Value],0.00)= 0,

_LatestValue,

SELECTEDVALUE('Result table'[Value]))

_______________________________________________________________________________________________________________

Instead of giving me the latest value, it only shows the maximum value over the years. 

The data should show from April 2019 each month onwards till Apr 2022 is 2.1, whereby May 2022 till May 2023 is 2.3, and June 2023 till March 2024 is 1.2. 

Here is the dataset: https://drive.google.com/file/d/1fR9R1Yl2Zs1NaE5zeBWEt5Hn8X08MZy-/view?usp=drive_link

Please help and point out to me where goes wrong. 

Thank you very much!

[Dangar332]

Hi, @AKath_12 

Can you check access permission , I'm not able to access your dataset.

[AKath_12]

Hi, 

No matter how I change the relationship, after deleting the relationship and re-creating a new one,  it just automatically goes back to the Result table filter Calendar with many to one cardinality.😞

[johnt75]

There should be a date column in your calendar table, with one date for each day of the years you need to cover. Link that column to a date column in your Result table. If there isn't a date column in your Result table, e.g. because there's just a year & month, create one using either DAX or Power Query set to the first day of the month and use that to link to the calendar table.

[AKath_12]

Hi ,

I have created a date ([Calendar]) in the Calendar table, and changed the column format, as below:-

and, there is a date column in my result table as well:-

when I tried to link these 2 columns, the relationship automatically changed to the Result table filter Calendar table again with Many to one cardinality. 😭😭

[johnt75]

How very strange. I cannot replicate this behaviour, when I use your code it still allows me to create proper one-to-many relationships in the right direction.

The only thing I can suggest is to modify your code a little. You don't need to add the [Calendar] column as the CALENDAR function already gives you a [Date] column. Also, there is no need for the SUMMARIZE - the table is already at the granularity of one day, which is what you need, so there is nothing for SUMMARIZE to do.

[AKath_12]

Hi,

Thanks for the suggestion. This is the relationship auto-corrected by the PBI. 

I got a better result displayed, however, still not what I am looking for, for example, 1.99 should carry forward to 202307 till 202403 and 1.22 from 202404 till 202412, however, this is not happening with this DAX formula. It displayed with the Max value 2.33 instead. Any idea how this can be adjusted? Thanks for your help. 

[johnt75]

The relationship looks correct now.

What is the purpose of 'Result table'[Location] > _byLocation? Should it not be = ?

[AKath_12]

= give me a result display in individual months only,

> Give me result display every month, 

any other DAX formula can help me fix my problem? 😖

[johnt75]

The relationship from Calendar to 'Result table' is wrong - 'Result table' is filtering Calendar not the other way around. Delete the relationship and create a one-to-many relationship from Calendar to 'Result table'[Completed date] and your code should work.