Calculating the number of days between two date columns

[Anonymous]

Need help in DAX (Power Query). I've tried a few options but keep on getting an error.  

1. Calculate the number of days between 2 dates in 2 date columns (I used DATESBETWEEN and DATEDIFF but getting errors in my DAX syntax)

2. Except for calculating the days between the two date columns per row, I want to calculate the number of days to month-end. Say the total number of days between the 2 dates  (date columns) are 30, then I want to calculate the number of days that can be allocated to the current month ( Month that transaction was initiated) and the number of the days that can be allocated to next month 

Column 1: 2021/06/06

Column 2: 2021/07/06

Total number of days: 30 days 

Number of days allocated to Jun: 24 days

Number of days allocated to Jul: 6 days

I was able to solve the problem with Excel but I am looking for a solution in Power Query as I think it will be more efficient than using formulas.

DATA
1stDate	2ndDate	Transaction Amount
2021/06/06	2021/07/06	30000
2021/07/23	2021/07/29	6000
2021/07/25	2021/08/03	9000
2021/08/04	2021/09/03	30000
2021/08/22	2021/09/10	19000
2021/09/05	2021/09/16	11000
2021/09/28	2021/10/03	5000
2021/10/01	2021/10/20	19000

[smpa01]

@Anonymous  attached pbix

[smpa01 · Accepted solution]

@Anonymous  Power Query and Dax are two different programming and I am guessing that you need a DAX solution, not that it can't be done by Power Query.

If you need a PQ solution give a shout.

The DAX solution is following

Measure =
COUNTX (
    ADDCOLUMNS (
        GENERATE (
            'Table',
            DATESBETWEEN ( 'Calendar'[Calendar_Date], 'Table'[1stDate], 'Table'[2ndDate] )
        ),
        "period",
            MAXX (
                FILTER (
                    'Calendar',
                    'Calendar'[Calendar_Date] = EARLIER ( 'Calendar'[Calendar_Date] )
                ),
                'Calendar'[Year_Month]
            )
    ),
    [period]
)

You need a Calendar table for this  with minimum columns like following

| Calendar_Date | Year_Month |
|---------------|------------|
| 2000-01-01    | 200001     |

Both the tables are disconnected tables with no relationship. The measure will give you following

[Anonymous]

Hey @smpa01 , thanks so much for your awnser!

Is there any way to transform this visualization table into a data table? Cause this solution worked very well, but I need to use the result to create charts and it give me wrong results as I cant use 1st date and 2nd date.

Or maybe, a measure that gives me the awsnser by just giving Year_month and the measure to a bar chart for example.

Thanks in advance!

[tferkel]

Is it possible to get the PQ solution for this?  The DAX solution works well but is a little slow with my data.

[searl]

Apologies - I can see this was asked over a year ago and this might not be the cleverest solution, but this is how I would calculate days;

In Power Query it should just be as easy as 

Duration.Days([2nd Date]-[1st Date])

to give you the number of days between 2 columns.
To calculate Month End, I would duplicate the column that you want to count from month end - so of it's 1st Date's month end, duplicate 1st Date. Or if thats the current month end, create a Todays date column using 

DateTime.Date(DateTime.LocalNow())

 Then, from the duplicated column Extract the Month End from the Transform Ribbon > Date > Month > End of Month.

Create a custom column again using the same code as the first column;

Duration.Days([Month End Date]-[1st Date])