Get previous months amount if missing from a table in a measure

[srpeters]

Hello,

I have a table like this example:

Some months are missing from this table meaning that if I wrote a measure like:

Amount vs Cost = AVERAGE(Table1[Running Amount]) - AVERAGE(Table2[Running Cost])

and I want to plot this measure in a graph, using a junction date table as its X axis, the AVERAGE(Table1[Running Amount]) would be considered blank for the months that exist in Table2 but not Table1 (and vice versa). Is it possible to instead consider the blank months to be the last amount prior to that month? 

 For example AVERAGE(Table1[Running Amount]) would equal 129 for 12/2023 when the Amount vs Cost is plotted on the chart. 

I have tried something like:

    Amount vs Cost =
      VAR RA = if(AVERAGE(Table1[Running Amount]) <> Blank(), AVERAGE(Table1[Running Amount]) , 

      LASTNONBLANKVALUE(Table1[Month], AVERAGE(Table1[Running Amount])), blank())

      VAR RC = if(AVERAGE(Table2[Running Cost]) <> Blank(), AVERAGE(Table1[Running Cost]) , 

      LASTNONBLANKVALUE(Table2[Month], AVERAGE(Table1[Running Cost])), blank())

    RETURN RA - RC

But this did not substitute the amounts and costs for blank months as their most recent months amounts and costs when plotted. 

Thank you in advance!

[Anonymous]

Hi @srpeters ,

We can try to use Lookupvalue to give the value of the blank date, here are some of my ideas to realize i

LastRunningAmount = 
    IF (
        ISBLANK(CALCULATE(AVERAGE('Table'[Running Amount]),'Table'[YearMonth (table1)]=MAX('Data Table'[YearMonth(DataTable)]))),
       LOOKUPVALUE(
            'Table'[Running Amount], 
            'Table'[Month], 
            MAXX(
                FILTER('Table', 
                'Table'[YearMonth (table1)] < MAX('Data Table'[YearMonth(DataTable)])), 
                'Table'[Month]
            )
        ),
     AVERAGE('Table'[Running Amount])
    )

The main thing we're trying to do here is to use a table of date bridges to determine which of our months have values and which don't, which can better help us with assignments.

LastRunningCost = 
IF (
        ISBLANK(CALCULATE(AVERAGE('Table (2)'[Running Cost]),'Table (2)'[YearMonth (table2)]=MAX('Data Table'[YearMonth(DataTable)]))),
        LOOKUPVALUE(
            'Table (2)'[Running Cost], 
            'Table (2)'[Month], 
            MAXX(
                FILTER(ALL('Table (2)'), 'Table (2)'[YearMonth (table2)] < MAX('Data Table'[YearMonth(DataTable)])), 
                'Table (2)'[Month]
            )
        ),
        AVERAGE('Table (2)'[Running Cost])
    )

I hope my thoughts can help you, if you have further questions, you can contact me at any time, I will reply to you in the first time, looking forward to your reply!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[srpeters]

@v-xingshen-msft I was able to replicate this solution with the sample data, but it did not seem to work with data that had multiple amounts for the same dates and the same dates for different categories. For example, if I use this sample data instead:

I get a "multiple values was supplied where a single value was expected" error message since there are now multiple amounts for the same date in some instances.

Is there any work around for this? 

Sorry about the initial sample data, I should have been more specific. 

[Anonymous · Accepted solution]

Hi @srpeters ,

Thank you very much for your reply, I have also simplified my code to ensure that when there is more than one value in a day, or more than one value in a month, we use the maximum value in that month as the value for the next vacant month.

LastRunningAmount(2) = 
    IF (
       ISBLANK(CALCULATE(AVERAGE('Table'[Running Amount]),'Table'[YearMonth (table1)]=MAX('Data Table'[YearMonth(DataTable)]))),
        MAXX(
            FILTER(
                ALL('Table'), 
                'Table'[YearMonth (table1)] < MAX('Data Table'[YearMonth(DataTable)])  
            ), 
           ( 'Table'[Running Amount]) 
        ),
        AVERAGE('Table'[Running Amount])
    )

I hope my thoughts have solved your problem, and if you have further difficulties with this issue, you can always contact me and I will get back to you as soon as I receive the message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[srpeters]

Hello,

I landed on something similar to:

 IF (

       ISBLANK(CALCULATE(AVERAGE('Table'[Running Amount]),'Table'[YearMonth]=MAX('Data Table'[YearMonth(DataTable)]))) ,

       CALCULATE(SUM(Table[Amount]), FILTER(ALLEXCEPT(Table, Table[Type]), Table[YearMonth] <= (MAX('Data Table'[YearMonth(DataTable)])))),

        CALCULATE(AVERAGE('Table'[Running Amount]),'Table'[YearMonth]=MAX('Data Table'[YearMonth(DataTable)]))

    )

This implementation worked better for my use case, however, you definitely answered my question and I would not have easily gotten to a solution without it!

Thank you so much!