Forum Discussion

AKath_12's avatar
AKath_12
Frequent Visitor
1 year ago

Help! How do I roll forward the latest value

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. 

 

 
 
Please help and point out to me where goes wrong. 

 

Thank you very much!

 

 

9 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    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.

  • AKath_12's avatar
    AKath_12
    Frequent Visitor
    1 year ago

    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's avatar
      johnt75
      Icon for Super User rankSuper User
      1 year ago

      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's avatar
        AKath_12
        Frequent Visitor
        1 year ago

        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. 😭😭