Forum Discussion

Birdjo's avatar
Birdjo
Icon for Resolver II rankResolver II
9 years ago
Solved

If This Month or This Week falls in Selected Date Ranges

Hello,

I am working on a report which shows which employees are on a leave. 
I am trying to develop some date intelligence like being able to choose this month, last mont, this week, next week, last week

My data looks like this:

 

I use formulas like this:

Week = 
IF(
    OR(YEAR(TODAY()) && WEEKNUM('ALL-EMPABS'[TODATE];21) = WEEKNUM(TODAY();21);
        YEAR(TODAY()) && WEEKNUM('ALL-EMPABS'[FROMDATE];21) = WEEKNUM(TODAY();21));
        "This Week";
IF(
    OR(YEAR(TODAY()) && WEEKNUM('ALL-EMPABS'[TODATE];21) = WEEKNUM(TODAY()-7;21);
        YEAR(TODAY()) && WEEKNUM('ALL-EMPABS'[FROMDATE];21) = WEEKNUM(TODAY()-7;21));
        "Last Week";
IF(
    OR(YEAR(TODAY()) && WEEKNUM('ALL-EMPABS'[TODATE];21) = WEEKNUM(TODAY()+7;21);
        YEAR(TODAY()) && WEEKNUM('ALL-EMPABS'[FROMDATE];21) = WEEKNUM(TODAY()+7;21));
        "Next Week";
        "Other Weeks")))

But what happens is it looks for a date in the from and to date columns.
If the date for and absence is like 01.02.2017 till 05.03.2017 and today is 21st of February, it doesn't give me this abscence as this week absence. 


I tryied using DATESBETWEEN but I guess this is not what it is made for.
I will appreciate every idea and advice about how to get this done.

Thanks in advance!

 

Need Help
  • JanMulkens's avatar
    JanMulkens
    9 years ago

    Hi Bird007,

     

    It seems that creating a date dimension would already solve part of the difficulty that you're having here.

     

     

    But RobJo found a possible solution that might help you out directly. Can you check this out and let us know if it is applicable for you?

    http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregation/td-p/40507

     

     

    We discussed this issue in the "Global Power BI - Forum Aid" event.

    Come join us at the new Microsoft pbiusergroup.com board for more info, help and fun & informative events

  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    You could try creating the following table which expands the leave date ranges and provides you the ability to count/sum the weekdays

     

    Leave Table = SELECTCOLUMNS(
            FILTER(
                CROSSJOIN('Leave',Dates) ,
                'Leave'[FromDate] <= 'Dates'[Date]
                && 'Leave'[ToDate] >= 'Dates'[Date]
                ),
               "EmployeeID" , [EmployeeID],
               "Absense Type", [AbsenceType] ,
               "From Date" , [FromDate] ,
               "To Date" , [ToDate] ,
               "Date on Leave" , [Date] ,
               "Is Weekday" , SWITCH(
                                WEEKDAY('Dates'[Date],3),
                                --- Saturday ---
                                5,0,
                                --- Sunday ---
                                6,0,
                                --- ELSE ---
                                1
                                )
                             )

4 Replies

  • JanMulkens's avatar
    JanMulkens
    Icon for Advocate I rankAdvocate I
    9 years ago

    Hi Bird007,

     

    It seems that creating a date dimension would already solve part of the difficulty that you're having here.

     

     

    But RobJo found a possible solution that might help you out directly. Can you check this out and let us know if it is applicable for you?

    http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregation/td-p/40507

     

     

    We discussed this issue in the "Global Power BI - Forum Aid" event.

    Come join us at the new Microsoft pbiusergroup.com board for more info, help and fun & informative events

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      You could try creating the following table which expands the leave date ranges and provides you the ability to count/sum the weekdays

       

      Leave Table = SELECTCOLUMNS(
            FILTER(
                CROSSJOIN('Leave',Dates) ,
                'Leave'[FromDate] <= 'Dates'[Date]
                && 'Leave'[ToDate] >= 'Dates'[Date]
                ),
               "EmployeeID" , [EmployeeID],
               "Absense Type", [AbsenceType] ,
               "From Date" , [FromDate] ,
               "To Date" , [ToDate] ,
               "Date on Leave" , [Date] ,
               "Is Weekday" , SWITCH(
                                WEEKDAY('Dates'[Date],3),
                                --- Saturday ---
                                5,0,
                                --- Sunday ---
                                6,0,
                                --- ELSE ---
                                1
                                )
                             )
      • Birdjo's avatar
        Birdjo
        Icon for Resolver II rankResolver II
        9 years ago

        Thanks to both of you!

        I tried both and accepted Phil_Seamark's solution because it excluded the weekends.

        Of course there's a simple way to exclude the weekands with the first solution, but Phil_Seamark's seems complete to me.

        Thanks again!