Forum Discussion

FILIPKACZMAR's avatar
FILIPKACZMAR
Advocate I
4 years ago
Solved

Counting days

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 🙂
  • tamerj1's avatar
    tamerj1
    4 years ago

    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
        )
    )

     

8 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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.
     
    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" ) )

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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

     

    • FILIPKACZMAR's avatar
      FILIPKACZMAR
      Advocate I
      4 years ago

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

      :(((