Forum Discussion

phill2cb's avatar
phill2cb
Frequent Visitor
5 years ago
Solved

Pull forward last entered value

Hello,

This table below is updated weekly with the total value as of that date. The table goes to the end of the year with the TargetValue filled in for the whole year and TotalValue is blank if that date has not happened yet.

Date TotalValue TargetValue
1/4/21 119,450,220 120,000,000
1/11/21 119,901,073 119,960,785
1/18/21 119,770,053 119,921,570
1/25/21 120,101,424 119,882,355
2/1/21 120,151,349 119,843,140
2/8/21   119,803,925
2/15/21  

 

119,764,710

 

This table is linked to a Calendar Table that lists every date and is plotted on the X Axis of a graph. I am looking for the graph to show the TotalValue for that CalendarDate and if it that date doesnt exist in the above table, grab the previous TotalValue. For example, I have the Total Value for 1/4 and 1/11, so 1/5 through 1/10 should display the value from 1/4. 

CalendarDate TotalValue
1/4/21 119,450,220
1/5/21 119,450,220
1/6/21 119,450,220
1/7/21 119,450,220
1/8/21 119,450,220
1/9/21 119,450,220
1/10/21 119,450,220
1/11/21 119,901,073
1/12/21 119,901,073
1/13/21 119,901,073

 

I found the below measure in a forum which worked for me but the data is blank after 2/8. How can I modify this to show the 2/1 value through the end of the year, then the 2/8 value through the end of the year next week, etc.

Latest Value =
VAR vCurrentDate =
MAX ( 'Calendar'[Date] )
VAR vMaxDateCurrentID =
CALCULATE (
MAX ( InventoryHistorical[Date]),
ALLSELECTED ( 'Calendar'[Date] ),
InventoryHistorical[Date] <= vCurrentDate
)
VAR vMaxValueCurrentID =
CALCULATE (
MAX ( InventoryHistorical[TotalValue] ),
ALLSELECTED ( 'Calendar'[Date] ),
InventoryHistorical[Date] = vMaxDateCurrentID
)
RETURN
vMaxValueCurrentID
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, phill2cb 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    You may create a measure as below.

    Result = 
var d = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        [Date]<=MAX('Calendar'[Date])&&
        [TotalValue]<>BLANK()
    )
)
return
CALCULATE(
    SUM('Table'[TotalValue]),
    FILTER(
        ALL('Table'),
        [Date]=d
    )
)

     

    Result:

     

    Rest Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    phill2cb , Try a measure like

     

    calculate(sum(Table[TotalValue]), filter(Table, Table[Date] = calculate(max(Table[Date]), filter(allselected(date), Date[Date]<= max(Date[Date])))) )

    • phill2cb's avatar
      phill2cb
      Frequent Visitor
      5 years ago

      That is only showing dates that have values entered.

      The measure I posted is giving this result but wont show data past 2/7

       

       

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, phill2cb 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    You may create a measure as below.

    Result = 
var d = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        [Date]<=MAX('Calendar'[Date])&&
        [TotalValue]<>BLANK()
    )
)
return
CALCULATE(
    SUM('Table'[TotalValue]),
    FILTER(
        ALL('Table'),
        [Date]=d
    )
)

     

    Result:

     

    Rest Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.