Previous Amount: Total row is not correct

[Jan_Trummel]

Hello community,

today I have a difficult question for you and I hope someone ca help me.

My Data

To get started, I want to show you my tables:

The table Orders

This is the fact-table. You can see, that for Product ID 2, there is no amount in February and March (keep that in mind, please).

The table Products

This is the dimension-table. You can see 3 products here.

The table Calendar

And I have a date-dimension, which stores a date period from January to April 2022.

The data modell

Here is my data modell. It's very simple:

Of course, Products and Orders are connected via columns Product ID and Product ID. 

Calendar and Orders are connected via Date and Order Date.

The task

When I show my data in a matrix, I get this report:

Like I said, there is no amount for Product B in February and March.

So now, here is the task:

If I don't have an amount in a month, I want to show the last amount.

The measure Previous Amount

In this video from Alberto Ferrari, I found a possible solution to get the last amount:

Optimizing LASTNONBLANK and LASTNONBLANKVALUE calculations 

Here is my measure Previous Amount:

Previous Amount = 
VAR LastVisibleDate = MAX('Calendar'[Date])
VAR PreviousOrderDate = 
    CALCULATETABLE(
        LASTNONBLANK('Calendar'[Date], [Amount]),
        'Calendar'[Date] < LastVisibleDate
    )
VAR Result = CALCULATE([Amount], PreviousOrderDate)
RETURN
Result

The measure Amount is this:

Amount = SUM(Orders[Amount])

When I put Previous Amount in the matrix, I get this report:

In February and March, the measure now shows the right amount for product B.

But the Total row isn't correct (look at the values circled in red).

Of course, in the context of the Total row, there is no product B in the months February and March, so the results are:

Total February = 900 + 700 = 1,600

Total March = 700 + 100 = 800

So, now here is the problem:

How can I write code, that calculates the right result:

Total February = 900 + 950 + 700 = 2,550

Total March = 700 + 950 + 100 = 1,750

I just tried it with SUMX and SUMMARIZE, but it doesn't work.

Do you have an idea?

Thanks a lot and have a nice day!

Greetings

[Jan_Trummel]

Hello @amitchandak ,

thank you for your answer. Unfortunatelly, that's not the right code.

Your measure returns a date value:

Do you have another idea?

[v-rzhou-msft · Accepted solution]

Hi @Jan_Trummel ,

I create a sample by the data you provided above. I think you can try this measure to achieve your goal.

M_Amount = SUM(Orders[Amount])

Previous Amount = 
VAR _GENERATE =
    GENERATE ( VALUES ( Products[Product Name] ), VALUES ( 'Calendar'[Month Nr] ) )
VAR _ADD =
    ADDCOLUMNS (
        _GENERATE,
        "Previous Amount",
            VAR _LastVisibleDate =
                CALCULATE ( MAX ( 'Calendar'[Date] ) )
            VAR _PreviousOrderDate =
                CALCULATETABLE (
                    LASTNONBLANK ( 'Calendar'[Date], [M_Amount] ),
                    'Calendar'[Date] < _LastVisibleDate
                )
            VAR _Result =
                CALCULATE ( [M_Amount], _PreviousOrderDate )
            RETURN
                _Result
    )
RETURN
    SUMX ( _ADD, [Previous Amount] )

Result is as below.

Best Regards,
Rico Zhou

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

[Jan_Trummel]

Hello @v-rzhou-msft ,

this is exactly what I was looking for! Thank you very much!

Greeting!

[amitchandak]

@Jan_Trummel , try like

Previous Amount =
VAR LastVisibleDate = MAX('Calendar'[Date])
VAR Result = CALCULATE(LASTNONBLANK('Calendar'[Date], [Amount]), filter(all('Calendar'[Date]) ,'Calendar'[Date] < LastVisibleDate))
RETURN
Result