Forum Discussion

Anders_Jensen's avatar
Anders_Jensen
Regular Visitor
9 years ago

Generate line chart based on timerange

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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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