Calculate Dossier Active Periods(Self made)

[Anonymous]

Hello Power BI members,

I have a table with some datacontent of self made periods(table1). 13 periods in a year of 4 weeks a period.

Another table contains the dossiers(table2) with begin and end date.

I am going to include the pbix file, but also give the result i am looking for.

Link to dropbox

Table 1: Periods

Periode begin | Period End | Period

1-1-2018           28-1-2018    201801

29-1-2018        25-2-2018     201802

26-2-2018       25-3-2018      201803

Table2: Dossiers

Dossiernumer    |  Begin date Dossier | End date Dossier

1                                 2-1-2018                 25-1-2018     

2                                5-1-2018                  24-2-2018

3                                6-1-2018                  24-3-2018

The result must contain the active periods of a dossier

number 1 will be1 periods

number 2 will be 2 periods

number 3 will be 3 periods

Second quesion:

Can this result be put in a visual with likeable fuctionality as YOY, MOM, QOQ or SAMEPERIOD?

So that the result will be:

                                        Difference Amount             Difference%

Period 201801 = 1

Period 201802 = 2,           1                                              100%

Period 201803 = 3            1                                              100%

In this way we can look at the differences between the periods.

Any kind help would be very appriciated.

[v-frfei-msft]

Hi @Anonymous ,

For  the 1st question, we can create a meausre as below.

Measure = 
CALCULATE (
    COUNTROWS ( Periods ),
    FILTER (
        Periods,
        'Periods'[PER_END] >= MAX ( 'Dossier'[Einddatum voorziening] )
            && Periods[PER_END] >= MAX ( 'Dossier'[Begindatum voorziening] )
    )
)

For the 2rd one, Sorrt I cannot get that, what is the logic of that? 

BTW, PBIX as attached.

[Anonymous]

@v-frfei-msft 

Sorry for the inconvenience.

i tried to be as simple as i good be in my question but i made it more difficult.

I checked the measure but sometime it misses a lot of periods.

I made a new PBIX with fewer data(TABLE dossiers). The PBIX has the same name and can be opend through the ealier posted LINK to dropbox. i will try to be more specific now.

Question: Every client has a dossier(picture column A with a begin and end date(Column B and C) at a producer(Column D)

The begin and end date are must be placed within periods(Column F,G) and then there must be an sum of 1s under each periods.

In the picture a have made the result clear(under the horizontal black line).

3 clients, 3 dossier placed within matching periods(F,G) under the corresponding period. sum at the bottom and at the right.

The second question is for workload purposes. How many dossiers where there in period? Is there an increase or decrease? between 1 period, between 13 periods etc...