Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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
Did you resolve the negatives?
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.
Thanks a lot, i feel there is a lot to learn.
where can i find such things to learn more in powerbi ?