Generate line chart based on timerange

[Anders_Jensen]

Hi,

Im currently in a situation where I would like to genererate a line chart based on the following data:

The output should be shown like this:

The issue is that i have like 200.000 rows, and the only way I can figure out to do is is to create like pr. day in range of each product. But this will be an issue when i talk about subscription that might have a active period on a year or more.

Is there any function in Power BI, so im not forced to generate alot of date for such a simple repport?

Maybe im to blind an the answer might be in front of me without knowing it.

Regards

Anders Jensen

[v-yulgu-msft]

Hi @Anders_Jensen,

In my test, I calculated data paritially, you can see the workaround below as a reference.

Create two date tables:

DateTable1=
    CALENDAR ( DATE ( 2016, 1, 1 ), MAX ( LineChart[Expire_date] ) )

DateTable2 =
    CALCULATETABLE ( DateTable1, DAY ( DateTable1[Date] ) = 1 )

In the source table, add serveral calculated columns and measures: 

Calculated columns: (in your scenario, you should calculate from "Effective for 1 month" to "Effective for 12 month")

Diff =
    DATEDIFF ( LineChart[Effective_date], LineChart[Expire_date], MONTH )

Effective for 1 month =
    IF ( LineChart[Diff] >= 1, 1, 0 )
Effective for 2 month =
    IF ( LineChart[Diff] >= 2, 1, 0 )
Effective for 3 month =
    IF ( LineChart[Diff] >= 3, 1, 0 )

Measures: (in your scenario, you should add 12 measures)

Measure 1 =
    CALCULATE ( SUM ( LineChart[Effective for 1 month] ), ALL ( LineChart ) )
Measure 2 =
    CALCULATE ( SUM ( LineChart[Effective for 2 month] ), ALL ( LineChart ) )
Measure 3 =
    CALCULATE ( SUM ( LineChart[Effective for 3 month] ), ALL ( LineChart ) )

Create some calculated tables:

Table1 =
    ADDCOLUMNS (
        LineChart,
        "Count1", [Measure 1],
        "Count2", [Measure 2],
        "Count3", [Measure 3]
    )

Table2 =
    UNION (
        ADDCOLUMNS ( SELECTCOLUMNS ( 'Table1', "Count", 'Table1'[Count1] ), "index", 1 ),
        ADDCOLUMNS ( SELECTCOLUMNS ( 'Table1', "Count", 'Table1'[Count2] ), "index", 2 ),
        ADDCOLUMNS ( SELECTCOLUMNS ( 'Table1', "Count", 'Table1'[Count3] ), "index", 3 )
    )

Table3 =
    SUMMARIZE ( 'Table2', 'Table2'[Date2], "Count", AVERAGE ( 'Table2'[Count] ) )

Table4 = CROSSJOIN(DateTable1,Table3)

Table5 =
    CALCULATETABLE (
        Table4,
        FILTER ( Table4, Table4[Date2].[MonthNo] = Table4[Date].[MonthNo] )
    )

In the line chart, put Table5[Date] into Axis and Table5[Count] into value.

Thanks,
Yuliana Gu