no of days in between two dates month wise

[kohlivinayak]

Hi I have a table like below 

Type  |  Start date    |  end date  

A       | 07-feb-2014 | 24-apr-2014

B       | 12-mar-2014 | 30-oct-2015

and i want output like 

type   |  Feb-2014  | March-2014 | April-2014 | 

A        |       22        |       31           |      24         |

B        |         0        |       20           |       30        |

its calculation of no of days type A was used in a particular month.

Thanking you in advance.

[v-yulgu-msft · Accepted solution]

Hi @kohlivinayak,

Suppose the original is called 'Source table'. 

Create a calendar table first:

Calendar =
CALENDAR (
    DATE ( YEAR ( MIN ( 'Source table'[Start date] ) ), MONTH ( MIN ( 'Source table'[Start date] ) ), 1 ),
    DATE ( YEAR ( MAX ( 'Source table'[end date] ) ), MONTH ( MAX ( 'Source table'[end date] ) ), 31 )
)

Filter above 'Calendar' table:

Calendar 2 =
FILTER (
    'Calendar',
    DAY ( [Date] )
        = CALCULATE (
            MAX ( 'Calendar'[Date].[Day] ),
            ALLEXCEPT ( 'Calendar', 'Calendar'[Date].[Month] )
        )
)

Cross Join 'Source table' and 'Calendar2'. And add a calculated column in new table.

Cross Join =
CROSSJOIN ( 'Source table', 'Calendar 2' )

Column1 =
IF (
    'Cross Join'[Date].[MonthNo] = 'Cross Join'[Start date].[MonthNo],
    DATEDIFF ( 'Cross Join'[Start date], 'Cross Join'[Date], DAY ) + 1,
    IF (
        'Cross Join'[Date].[MonthNo] = 'Cross Join'[end date].[MonthNo],
        'Cross Join'[end date].[Day],
        IF (
            'Cross Join'[Date].[MonthNo] > 'Cross Join'[Start date].[MonthNo]
                && 'Cross Join'[Date].[MonthNo] < 'Cross Join'[end date].[MonthNo],
            'Cross Join'[Date].[Day],
            0
        )
    )
)

Use a Matrix to host data from 'Cross Join' table.

Best regards,

Yuliana Gu

[afernandez99012]

Hi,

I've followed the steps, but my output in most cases for column 1 is a negative, any idea why this maybe?

Thanks in Advance

[Sweetland]

Did you resolve the negatives?

[Sweetland]

I think I have worked it out!! This code is aimed at dates that are all in the same year!! so there needs to be an additional bit of code to check if not only it is the same month, it must be the same year. 

[kohlivinayak]

Thanks a lot, i feel there is a lot to learn.

where can i find such things to learn more in powerbi ?