Trend from History Table v2.0

[bmyton]

Good afternoon Community,

In 2017 @v-chuncz-msft provided this solution to creating a history table:

https://community.powerbi.com/t5/Desktop/Trend-from-History-Table/m-p/200304/highlight/true#M88115

Today I need help expanding that solution to support one additional column in the source data.

Table Structure:

Customer ID (int) | Subscribed From (date) | Subscribed Until (date) | Subscription Type (string)

I would like the resulting table to include a column for each distinct Subscription Type. I could achieve this by duplicating that portion of the ADDCOLUMNS argument manually for each Subscription Type, but I would prefer to have a dynamic solution.

I imagine this is a place where I would need the DISTINCT function, but I can't quite figure that out.

Table =
ADDCOLUMNS (
    CALENDARAUTO (),
    EachDistinctServiceType, CALCULATE (
        DISTINCTCOUNT ( Table1[Customer ID] ),
        FILTER (
            Table1,
            Table1[Subscribed From] <= [Date]
                && (
                    Table1[Subscribed Until] > [Date]
                        || ISBLANK ( Table1[Subscribed Until] )
                && Table1[Service Type] = "EachDistinctServiceType
                )
        )
    )
)

The resulting table should look like this:

Date	ServiceTypeA	ServiceTypeB	ServiceTypeC	...
1/1/2021	50	300	100	...
1/2/2021	75	200	250	...
1/3/2021	100	100	210	...

[lbendlin]

You're nearly there.  You can use VALUES() to get the service type values, or DISTINCT(), or SUMMARIZE() etc - many ways to do it.

However,  you need to keep in mind how visual elements are calculated.  There are separate calculations for the individual cells, the row and column totals, and the grand total.  Write your measure accordingly and you can have any number of service types processed automatically.