Drilling down periodical time data (subscriptions with start & end date) per month?

Question

I'm currently struggling with following request in PowerBI:

I have two CSV files as PowerBI queries, one which defines fiscal months, and another one which lists all subscriptions including start and end date:

Fiscal month CSV:

Month      Fiscal Start    Fiscal End
January    03.01.2016      04.02.2016
February   05.02.2016      03.03.2016
March      04.03.2016      06.04.2016
April      07.04.2016      02.05.2016
May        03.05.2016      06.06.2016
June       07.06.2016      03.07.2016
July       04.07.2016      05.08.2016
August     06.08.2016      02.09.2016

Subscription CSV:

Account-ID   Subscription-Start    Subscription-End    Item Count
101          08.01.2016            07.02.2016          5
102          15.01.2016            14.03.2016          3
103          05.01.2016            04.06.2016          10
101          08.02.2016            07.03.2016          3
104          10.04.2016            09.05.2016          5
105          16.04.2016            15.07.2016          2

My challenge now is to drill down all subscription item counts per fiscal month as a powerBI table.

Note: an Item Count is valid for a fiscal month if its Subscription-Start < Fiscal End and its Subscription-End > Fiscal End. (Example: A subscription from 15.01.2016 - 14.02.2016 should be counted in january, but not in february)

PowerBI table (schematical example):

Month      Item Count      
January    18     
February   16     
March      10    
April      17  
May        12     
June       2      
July       0       
August     0

How can I implement this report in PowerBI? THX in advance for your help and BR bdriven

How do you want to handle scenarios like your Account-ID 101 - the Subscription dates overlap multiple Fiscal periods? — Mike Honey
Account-ID 101 should count as 5 Items in January, Account-ID 102 should count as 3 items in January and 3 items in February — Into Numbers

Into Numbers Into Numbers · Accepted Answer · 2016-07-19T19:13:08

I've found following solution for my problem:

First I've created a new Table and made a crossjoin of the two queries. Then I've filtered for the lines, where my Subscription Start was before the Fiscal Month End and Subscription End was after the Fiscal Month End.

Based on this new table I can create all respective reports.

Example Code see below:

Fiscal Month Report = 
FILTER(
    CROSSJOIN(
        ALL('Fiscal_month');
        ALL('Subscription')
    );
    ('Subscription'[Subscription-Start] < 'Fiscal_month'[Fiscal End] && 'Subscription'[Subscription-End] > 'Fiscal_month'[Fiscal End])
)

Drilling down periodical time data (subscriptions with start & end date) per month?

1 Answers