Continue Rolling Value by Month Until A New Value Appears

[Be-ro92]

I would like to show a sales opportunity amount by month until a new amount appears to demonstrate the historical pipeline of sales opportunities.

Whether it's done via DAX or Power Query, any assistance would be greatly appreciated!

Thanks!

Opportunity	Opportunity Date	Pipeline Amount
Opportunity 1	2023-04-01	$5,000
	2023-05-01	$5,000
	2023-06-01	$5,000
Opportunity 1	2021-07-01	$11,000
	2021-08-01	$11,000
	2021-09-01	$11,000
	2021-10-01	$11,000
	2021-11-01	$11,000
	2021-12-01	$11,000
	2022-01-01	$11,000
Opportunity 1	2022-02-28	$12,000

[Be-ro92]

Here is a screenshot of what I am trying to achieve. 

[v-yilong-msft · Accepted solution]

Hi @Be-ro92 ,

I create a table as you mentioned.

Then I create a new table and here is the DAX code.

Table 2 =
VAR _vtable =
    FILTER (
        CROSSJOIN (
            'Table',
            FILTER (
                CALENDAR ( MIN ( 'Table'[Opportunity Date] ), MAX ( 'Table'[Nextdate] ) ),
                DAY ( [Date] ) = 1
            )
        ),
        [Date] >= 'Table'[Opportunity Date]
            && [Date] < 'Table'[Nextdate]
    )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            _vtable,
            "_outcome",
                IF (
                    MINX ( FILTER ( _vtable, [Index] = EARLIER ( [Index] ) ), [Date] ) = [Date],
                    [Opportunity Date],
                    [Date]
                )
        ),
        [Index],
        "Product Type", [Opportunity ],
        "Pipeline Date", [_outcome],
        "Pipeline amount", [Pipeline Amount]
    )

Finally you will get what you want.

Best Regards

Yilong Zhou

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

[Be-ro92]

This formula is almost there. I noticed that from Jan 2022 to March 2022 that March 2022 does not appear.

And then there is no data from March 2022 to March of 2023.

How can I add this data in between those dates?