Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Excel date formula converted to DAX

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 d...
Need Help
ibarrau's avatar
ibarrau
Super User
7 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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!