Distribute total days in months

[dantedg1]

Hello,

Currently I am working on a project in which we have both a start and an end date as data. How can I distribute the days in months to then graph each month?

For example:

Begining Date: January 1st 2018

End Date: April 17 2018

Total days: 107 days

Month                     Days

January                     31

February                   28

March                       31

April                         17

I would really appreciate the support.

[Ashish_Mathur]

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

[dantedg1]

Hi Ashish,

Thank you for your support. The problem is that i have lots of data, for example:

Begininig Date         End Date             Days

01/01/2018              02/20/2018             51

02/02/2018              02/25/2018             24

01/04/2018              05/05/2018             35

Desired output:

Days in January= 58

Days in February= 44

Days in March = 0

Days in April = 30

Days in may 5

I would really appreciate if you could help me out with this issue. Thanks again.

[Ashish_Mathur]

Hi,

That is exactly what my solution will do.  Just add more beginning and ending dates and click on refresh.

[MFelix]

Hi @dantedg1,

Create a calendar table without any relationship with the fact table then add the following measure:

days =
CALCULATE (
    COUNTROWS ( ALLSELECTED ( DimDate[Date] ) );
    FILTER ( DimDate; DimDate[Date] >= MIN ( Projects[Start Date] ) );
    FILTER ( DimDate; DimDate[Date] <= MAX ( Projects[End Date] ) )
)

Add in a tabel visual the months / years from the Calendar table and the measure final result below:

As you can see it works for the full details

Regards,

MFelix

[dantedg1]

Thanks so much

[dantedg1]

Hello,

Currently I am working on a project in which we have both a start and an end date as data. How can I distribute the days in months to then graph each month?

For example:

Begining Date: January 1st 2018

End Date: April 17 2018

Total days: 107 days

Month                     Days

January                     31

February                   28

March                       31

April                         17

I would really appreciate the support.