Replicating current month column's data to future month

[mohdk52]

Hi team,

I am working on a project where there are sales value for Jan and Feb 2022.

I have to add the data from Mar'22 to Dec'22 - considering the numbers from FEB'22 and multiplying them by 1.5.

Kindly let me know how to achieve this?

Many thanks in advance.

[amitchandak]

@mohdk52 , Try a measure like

calculate(lastnonblankvalue('Date'[Month Year], [meausre]), filter(all('Date'), 'Date'[Date] >= eomonth(today(),-1) && 'Date'[Date] <= max('Date'[Date])))

[mohdk52]

Thanks @amitchandak But unfortunately it doesnt seem to be working.

Apologies If i didnt give the proper example before but in below example table.

Jan and Feb are actuals. And my dataset has data only for these 2 months.

Mar numbers are derived from Feb*1.5.

Apr numbers are derived from Mar*1.5.

I need to have this into a matrix chart.

Month/Sales	JAN	FEB	MAR	APR
Region1	2000	3000	4500	6750
Region2	3500	2500	3750	5625

[v-yanjiang-msft · Accepted solution]

Hi @mohdk52 ,

According to your description, here's my solution.

1.Create a new month table, don't make relationship with the fact table.

2.In Power Query, add an index column.

3.Create a measure.

Sales Measure =
VAR _Sales =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] = MAX ( 'Month'[Month] )
                && 'Table'[Region] = MAX ( 'Table'[Region] )
        ),
        'Table'[Sales]
    )
VAR _Last =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                = MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        NOT ISBLANK ( 'Table'[Sales] )
                            && 'Table'[Region] = MAX ( 'Table'[Region] )
                    ),
                    'Table'[Index]
                )
        ),
        'Table'[Sales]
    )
VAR _Diff =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] = MAX ( 'Month'[Month] )
                && 'Table'[Region] = MAX ( 'Table'[Region] )
        ),
        'Table'[Index]
    )
        - MAXX (
            FILTER (
                ALL ( 'Table' ),
                NOT ISBLANK ( 'Table'[Sales] )
                    && 'Table'[Region] = MAX ( 'Table'[Region] )
            ),
            'Table'[Index]
        )
RETURN
    IF ( NOT ISBLANK ( _Sales ), _Sales, _Last * POWER ( 1.5, _Diff ) )

Put the new month column, the measure and Region in the matrix, get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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