Maximum Value from the previous calendar month

[Anonymous]

Hi Guru's,
Am facing a issue in geeting maximum from the previous available values in the the column 

EG: 

In the Column E (Total) where ever the rows are blank, i need to populate maximum value with in the Column E(Total) until that calendar Key , Ex: in Row 18 should be populated with 3436 as this is maximum value for the calendar key 20200201(Feb 2020), in the same way , row 22 , 23 and 24 should be populated by 3524. Can you please help me on this.

Kind Regards,

Dimpu

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

You can also create a Calculated column.

Fill with Previous Month =

IF (

    ISBLANK ( 'Table'[Total] ),

    CALCULATE (

        MAX ( 'Table'[Total] ),

        FILTER (

            ALL ( 'Table' ),

            NOT ( ISBLANK ( 'Table'[Total] ) )

                && 'Table'[CalendarKey] < EARLIER ( 'Table'[CalendarKey] )

        )

    ),

    'Table'[Total]

)

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

If months within the year are in ascending order and the figures in the total column are also in ascending order, then the Data > Fill Down option in the Query Editor works ver well.

Hope this helps. 

[daxer-almighty]

@Anonymous 

I'd suggest doing it in Power Query. This calculation belongs to Power Query, not DAX.