Power BI date extrapolation

Hi Anonymous ,

At first, you need a calendar table.

Date =
CALENDAR ( MIN ( 'Status'[Start] ), MAX ( 'Status'[End] ) )

Then add a column to get YEAR&MONTH.

YM =
YEAR ( 'Date'[Date] ) * 100
    + MONTH ( 'Date'[Date] )

Then add column “startYM” and column “endYM”  in another table which contains names.

StartYM =
YEAR ( 'Status'[Start] ) * 100
    + MONTH ( 'Status'[Start] )

EndYM =
YEAR ( 'Status'[End] ) * 100
    + MONTH ( 'Status'[End] )

Cross join two tables.

Table =
CROSSJOIN ( DISTINCT ( SELECTCOLUMNS ( 'Date', "YM", 'Date'[YM] ) ), 'Status' )

Add a new column to get the count and use a new measure to sum it.

count =
IF ( 'Table'[YM] >= 'Table'[StartYM] && 'Table'[YM] <= 'Table'[EndYM], 1, 0 )

Measure =
SUM ( 'Table'[count] )

Now , you can use visuals to get result.

Best Regards,

Eads

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