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.
I've just recently begun using Power BI and DAX for my new job, so if this is a super simple or stupid fix I apologize.
I'm trying to create a column (NumDays) that calculates the number of days within a date range, and a column (NumWorkDays) that calculates the number of work days between the date range. I have a table (Calendar) that contains a list of dates and indicates with 1 or 0 whether it's a work day or not (Mon-Fri). In a separate table (Shipments), I have two date values I'm trying to find the number of work days between: DateOfDelivery and DateOfSale.
Here are my DAX formulas for both columns...
NumDays:
NumDays = IF(ISBLANK('Shipments'[DateOfDelivery]), -1, CALCULATE(COUNTROWS('Calendar'),DATESBETWEEN('Calendar'[Dates],'Shipments'[DateOfSale], 'Shipments'[DateOfDelivery])))
NumWorkDays:
NumWorkDays = IF(ISBLANK('Shipments'[DateOfDelivery]), -1, CALCULATE(SUM('Calendar'[IsWorkDay]),DATESBETWEEN('Calendar'[Dates],'Shipments'[DateOfSale], 'Shipments'[DateOfDelivery])))
Whenever I try to run either formula, I get this error:
"An invalid numeric representation of a date value was encountered."
And that's it. I've gone through both tables looking for weird values, and I've double checked the data types to make sure they are datetimes and not something funky. Honestly I'm pretty stumped here, so any help would be appreciated. Thanks in advance!
**Edit: I've tried breaking down the parts of each formula to try and pinpoint where the error is being caused. It seems like it's originating from the DATESBETWEEN() function, but I can't narrow it down to a particular column or table causing the issue. Is this just a known issue with that function??
Solved! Go to Solution.
If you want to use in a calculated column, change to this:
NumDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>Shipments[DateofSale] && Calendario[Date]<= Shipments[DateofDelivery]))
NumWorkDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>Shipments[DateofSale] && Calendario[Date]<=Shipments[DateofDelivery]&& Calendario[Workdays]=1))
Try this
NumDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>MIN(Shipments[DateofSale]) && Calendario[Date]<=MAX(Shipments[DateofDelivery])))
NumWorkDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>MIN(Shipments[DateofSale]) && Calendario[Date]<=MAX(Shipments[DateofDelivery])&& Calendario[Workdays]=1))
Perhaps something like this would work.
NumDays =
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Dates] >= MIN ( 'Shipments'[DateOfSale] )
&& 'Calendar'[Dates] <= MAX ( 'Shipments'[DateOfDelivery] )
)
)
@sdjensen Thanks for the reply! Unfortunately, that didn't quite solve my issue. Rather, it caused the column to calculated the exact same number for each row, causing some weird results in my data. I'll play around with the formula you suggested a bit more, though, as it eliminated the error I was getting!
If you want to use in a calculated column, change to this:
NumDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>Shipments[DateofSale] && Calendario[Date]<= Shipments[DateofDelivery]))
NumWorkDays = CALCULATE(COUNTROWS(Calendario);filter(Calendario;Calendario[Date]>Shipments[DateofSale] && Calendario[Date]<=Shipments[DateofDelivery]&& Calendario[Workdays]=1))
This worked great for me! Thanks!
Thanks for this tip.
I have another challange with dates. The data is based on two tables (no relationship)
1. SCHEDULE TABLE
Table: Tasks
Fields: Task Name, Start Date, Finish Date, Project
2. CALENDAR TABLE
Table: Calendar
Fields: Date, Month, Week
My question is how can I utilize your measure formula to visualize number of days, timephased (split by e.g. month and week) by project. We need a pivot like report and a combination chart where days are summarized by month (columns).
Any tip on how to resolve this would help a lot.
Thanks
@Vvelarde This worked great, thank you! And yeah, you're probably right about using a measure rather than a column. I tried to implement it as a measure initially, but couldn't quite get it to calculate the numbers I wanted. I'm not super familiar with the different usecases for calculated measures vs. columns, but I'll definitely have to look further into that. Thanks again!
Use a measure not a calculated column
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.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |