topic Re: Counting days in DAX Commands and Tips

Counting days

FILIPKACZMAR — Thu, 26 May 2022 13:17:50 GMT

Hello everyone!!!

I am new at Power BI community (learning) and I have got a little problem.

At my new work I got the task to repair our PowerBI dashboard.

I have to count days between two dates, and I wrote this code:

Obliczanie bez weekendow =

COUNTROWS (

FILTER (

ADDCOLUMNS (

CALENDAR ( 'SQA list'[End of Inspection].[Date], 'SQA list'[Delivered Report].[Date] ),

"Day of Week", WEEKDAY ( [Date], 2 )

[Day of Week] <> 6

&& [Day of Week] <> 7

)

And I got error, that start date could not be later than end day. In some rows it is like this, so why error is everywhere?

I am thinking about using IF function, to make program miss these rows. But I dont know how.

Can you help me?

Thank you very much all 🙂

Re: Counting days

Whitewater100 — Thu, 26 May 2022 13:31:24 GMT

Hi:

You can use DATESBETWEEN function:

For example:

DatesBtwn = DATESBETWEEN(Calendar[Date], [End of Inspection], [Delivered Report])

I will attach a report that shows a few examples of using either DATESBETWEEN or DATESINPERIOD. You should create a Date Table (MODELING>NEW TABLE>

and mak as Date Table and connect to Fact Table. This will allow you to do time intel calculations.

https://drive.google.com/file/d/1cNo0LnI91xyzJa02tcVirlRzosjA3A8N/view?usp=sharing

Date Table Code:

Dates = ADDCOLUMNS ( CALENDAR (FIRSTDATE(Sheet1[ Date]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )

Re: Counting days

tamerj1 — Thu, 26 May 2022 14:35:25 GMT

FILIPKACZMAR

Yes you can wrap CALENDAR with IF like

If ( 'SQA list'[End of Inspection].[Date] < 'SQA list'[Delivered Report].[Date],
CALENDAR ( 'SQA list'[End of Inspection].[Date], 'SQA list'[Delivered Report].[Date] ),
"Day of Week", WEEKDAY ( [Date], 2 )
))

the error will go but not sure about the results

Re: Counting days

FILIPKACZMAR — Mon, 30 May 2022 08:30:45 GMT

THX for help, but I have got another error with code: to much argument, max for if is only 3

:(((

Re: Counting days

tamerj1 — Mon, 30 May 2022 08:33:44 GMT

FILIPKACZMAR

Please paste the complete code in a reply in order to check it.

Re: Counting days

FILIPKACZMAR — Mon, 30 May 2022 08:59:03 GMT

COUNTROWS (

If ( 'SQA list'[End of Inspection].[Date] < 'SQA list'[Delivered Report].[Date],

CALENDAR ( 'SQA list'[End of Inspection].[Date], 'SQA list'[Delivered Report].[Date] ),

"Day of Week", WEEKDAY ( [Date], 2 )

))

Re: Counting days

FILIPKACZMAR — Mon, 30 May 2022 09:00:25 GMT

last [Date] is not collored

Re: Counting days

tamerj1 — Mon, 30 May 2022 10:25:49 GMT

Hi FILIPKACZMAR
Please use the following

= VAR First = 'SQA list'[End of Inspection].[Date] VAR Last = 'SQA list'[Delivered Report].[Date] VAR First1 = IF ( First <= Last, First, Last - 1 ) VAR Last1 = IF ( First <= Last, Last, First1 ) RETURN COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( First1, Last1 ), "Day of Week", WEEKDAY ( [Date], 2 ) ), [Day of Week] <> 6 && [Day of Week] <> 7 ) )

Re: Counting days

FILIPKACZMAR — Tue, 31 May 2022 06:34:50 GMT

thx for help, but it is still not working... have you got maybe 5 min to call you teams? I will send you gift from poland 🙂