cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

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:

bdf3f3bc6e

 

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!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Advocate I
Advocate I

Re: If This Month or This Week falls in Selected Date Ranges

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-Aggregat...

 

 

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



Please mark my reply as the solution if it help you out.
Also check out www.globalpowerbi.com for info on Global Power BI, a Virtual User Group.

View solution in original post

Highlighted
Microsoft
Microsoft

Re: If This Month or This Week falls in Selected Date Ranges

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Highlighted
Resolver II
Resolver II

Re: If This Month or This Week falls in Selected Date Ranges

Up!

Highlighted
Advocate I
Advocate I

Re: If This Month or This Week falls in Selected Date Ranges

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-Aggregat...

 

 

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



Please mark my reply as the solution if it help you out.
Also check out www.globalpowerbi.com for info on Global Power BI, a Virtual User Group.

View solution in original post

Highlighted
Microsoft
Microsoft

Re: If This Month or This Week falls in Selected Date Ranges

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Resolver II
Resolver II

Re: If This Month or This Week falls in Selected Date Ranges

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! 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors