Deferred Revenue - Using Colum Headers in a filter

[Anonymous]

Hi all,

New to PowerBi so I'm hoping someone can help here. I have a table of products that include an amount, contract length and amount paid. I want to build a deferred and current month released revenue schedule per account. Products start at different times in the year for each account. Here is an example dataset. 

The type of visualisation I want to see is below. My idea was to have a table in PowerBi with the month-year as the colum header and try to filter on it but there might be a better way of doing it. 

Thanks in advance

[Anonymous]

Hi @Anonymous ,

I suggest you to create an unrelated Calendar table first ,then create a measure and show your result in a matrix.

Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "YearMonthName", FORMAT ( [Date], "MMM-YYYY" ),
    "MonthStart", EOMONTH ( [Date], -1 ) + 1
)

Measure:

Measure
    =
    VAR _GENERATE =
        GENERATE (
            VALUES ( 'Table'[Account] ),
            SUMMARIZE ( 'Calendar', 'Calendar'[YearMonthName], 'Calendar'[MonthStart] )
        )
    VAR _ADDSUM =
        ADDCOLUMNS (
            _GENERATE,
            "Sum",
                SUMX (
                    FILTER (
                        'Table',
                        'Table'[Account] = EARLIER ( [Account] )
                            && 'Table'[Start] <= EARLIER ( [MonthStart] )
                            && 'Table'[End] > EARLIER ( [MonthStart] )
                    ),
                    'Table'[Monthly]
                )
        )
    RETURN
        SUMX ( _ADDSUM, [Sum] )

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.

[Anonymous]

Thanks RicoZhou, almost there, only issue I can't seem to fix is the YearMonthName is being displayed Month and year by by Month. Rather than Jan- Dec