Excel date formula converted to DAX

[Anonymous]

Hi!

I've got a formula in Excel that can look at 2 dates, then calculate the number of days any given month that the two dates occur in.

So in the example in the image, the two dates are 25/02/19 and 01/04/19. The month I'm interested in is April, as marked by Apr in cell C3, which returns 15 days. The reason for the date in D2 is if there is no second date, so it will default to the value in D2.

This formula is really useful when I've got thousands of date pairs in my data and want to know across each month how many days occured in that given month.

I'm now using power BI and can't figure out how to do this with DAX. Can anyone help?

Thanks in advance

[ibarrau]

Hi, first of all you should see how to define the date in D2. I recommend to have it as a datasource in excel or automatically calculated in PowerQuery or DAX ( in case it is now o last N days) in order to make it scalable in future.

Once you have that value we can build a calculated column in dax for the table that contains the dates from column A and B.

NewColumn = 
VAR D2_Value = MAX ( TableWithDate[ColumnD2] )
// In case you are going to hardcode use this instead:
VAR D2_Value = DATE( 2019 , 9 , 3 )

RETURN

IF ( 
    ISBLANK ( Table[ColumnB] ) ,
    DATEDIFF ( Table[ColumnA] , D2_Value , DAY ) , 
    DATEDIFF ( Table[ColumnA] , Table[ColumnB] , DAY ) 
)

This will onle work if the column A and B are marked as Date Type in Power Bi. You can find more information of the function here:

https://dax.guide/datediff/

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for responding to my post

So this doesn't give me the desired output unfortunately. 

I'm not looking for the number of days between 2 dates. I'm looking for the number of days events occur for each month of year, based on the start and end date.

Here are some examples  

So there is one event, which lasted from 24/04/19 to 13/06/19, which affected 3 months. 5 days were in April, 31 days in May and 13 in June. There are two formats this could take, the first one is preferreed. 

Hope this makes it clearer?

thanks!