Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Deferred Revenue - Using Colum Headers in a filter

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

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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