Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@JohannMor 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
@JohannMor attached pbix
@JohannMor 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
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!
Is it possible to get the PQ solution for this? The DAX solution works well but is a little slow with my data.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.