Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MrFahrenheit
Helper I
Helper I

Convert Monthly Target to Daily Target with a twist...

I have a montly target of 1,000 hours and would like to break that down to a daily target that only increases on weekdays (Mon to Friday). 

 

What I've been able to do by myself is break down the 1000 hours into a daily target (1000 hours / # of weekdays in the month) * Current day of the month. Unfortunately, this results in the daily target being too high. The other option I tried was (1000 hours / # of days in the current month) * day of the current month. This resulted in a target which is too low. I'm trying to find a happy "medium" by forcing the sum to rise only on weekdays. 

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

What you want is 1000 hrs * ( # weekdays so far this month ) / ( # weekdays in the month ).

 

This is easier to calculate if you have an IsWeekday column on your date dimension table. For example,

IsWeekday = IF ( WEEKDAY ( Dim_Date[Date] ) IN { 2, 3, 4, 5, 6 }, 1, 0 )

You'll also want a column that groups the dates into months. For example,

MonthEnd = EOMONTH ( Dim_Date[Date], 0 )

 

Then your target might look like this (if it were a calculated column):

Target =
VAR CurrDate = Dim_Date[Date]
VAR CurrMonth = Dim_Date[MonthEnd]
VAR SubTable = FILTER ( Dim_Date, Dim_Date[MonthEnd] = CurrMonth )
RETURN
    1000
        * DIVIDE (
            SUMX ( FILTER ( SubTable, Dim_Date[Date] <= CurrDate ), Dim_Date[IsWeekday] ),
            SUMX ( SubTable, Dim_Date[IsWeekday] )
        )

View solution in original post

MrFahrenheit
Helper I
Helper I

I am the OP and I didn't mark the post suggested as the solution, as the solution. I did get some inspiration from the post but didn't really use it. 

 

Here is what worked for me - I use the date table from "Enterprise DNA" which you can read about here:

Extended Date Table Power Query M Function - Enterprise DNA

If you use the same date table, you can follow most of my formula below exactly.

 

Here is the measure that converts a montly target to a daily target, that only goes up on work days of the month. Note that "Monthly Hours Target'[Target Month]" is the column in the "target" table which has the target month in it (Jan, Feb, etc..). "Monthly Hours Target'[Target Hours]" is the column in the target table which has the hours in it.

I just drop this measure onto a bar chart, along with the "Target" and, each work day, the daily target will rise according to the workday of the month. 

 

Daily Target =
VAR MonthlyTarget =
CALCULATE(SUM('Monthly Hours Target'[Target Hours]),
FILTER(ALL('Monthly Hours Target'[Target Month]),
'Monthly Hours Target'[Target Month] = DATE(YEAR(TODAY()), MONTH(TODAY()), 30)))
VAR CountWorkingDays =
CALCULATE (
    COUNTROWS ( 'Date Table' ),
    FILTER (
        'Date Table',
        'Date Table'[Date] < DATE(Year(TODAY()), MONTH(TODAY()), DAY(TODAY()))
            && 'Date Table'[Month] = MONTH ( TODAY () )
            && 'Date Table'[Day of Week Number] <= 5
    )
)
VAR WeekdaysInMonth =
CALCULATE(DISTINCTCOUNT('Date Table'[Date]), 'Date Table'[Month] = MONTH(TODAY()), 'Date Table'[Year] = YEAR(TODAY()), 'Date Table'[IsWeekDay] = true)

Return

MonthlyTarget * (CountWorkingDays / WeekdaysInMonth)

View solution in original post

2 REPLIES 2
MrFahrenheit
Helper I
Helper I

I am the OP and I didn't mark the post suggested as the solution, as the solution. I did get some inspiration from the post but didn't really use it. 

 

Here is what worked for me - I use the date table from "Enterprise DNA" which you can read about here:

Extended Date Table Power Query M Function - Enterprise DNA

If you use the same date table, you can follow most of my formula below exactly.

 

Here is the measure that converts a montly target to a daily target, that only goes up on work days of the month. Note that "Monthly Hours Target'[Target Month]" is the column in the "target" table which has the target month in it (Jan, Feb, etc..). "Monthly Hours Target'[Target Hours]" is the column in the target table which has the hours in it.

I just drop this measure onto a bar chart, along with the "Target" and, each work day, the daily target will rise according to the workday of the month. 

 

Daily Target =
VAR MonthlyTarget =
CALCULATE(SUM('Monthly Hours Target'[Target Hours]),
FILTER(ALL('Monthly Hours Target'[Target Month]),
'Monthly Hours Target'[Target Month] = DATE(YEAR(TODAY()), MONTH(TODAY()), 30)))
VAR CountWorkingDays =
CALCULATE (
    COUNTROWS ( 'Date Table' ),
    FILTER (
        'Date Table',
        'Date Table'[Date] < DATE(Year(TODAY()), MONTH(TODAY()), DAY(TODAY()))
            && 'Date Table'[Month] = MONTH ( TODAY () )
            && 'Date Table'[Day of Week Number] <= 5
    )
)
VAR WeekdaysInMonth =
CALCULATE(DISTINCTCOUNT('Date Table'[Date]), 'Date Table'[Month] = MONTH(TODAY()), 'Date Table'[Year] = YEAR(TODAY()), 'Date Table'[IsWeekDay] = true)

Return

MonthlyTarget * (CountWorkingDays / WeekdaysInMonth)
AlexisOlson
Super User
Super User

What you want is 1000 hrs * ( # weekdays so far this month ) / ( # weekdays in the month ).

 

This is easier to calculate if you have an IsWeekday column on your date dimension table. For example,

IsWeekday = IF ( WEEKDAY ( Dim_Date[Date] ) IN { 2, 3, 4, 5, 6 }, 1, 0 )

You'll also want a column that groups the dates into months. For example,

MonthEnd = EOMONTH ( Dim_Date[Date], 0 )

 

Then your target might look like this (if it were a calculated column):

Target =
VAR CurrDate = Dim_Date[Date]
VAR CurrMonth = Dim_Date[MonthEnd]
VAR SubTable = FILTER ( Dim_Date, Dim_Date[MonthEnd] = CurrMonth )
RETURN
    1000
        * DIVIDE (
            SUMX ( FILTER ( SubTable, Dim_Date[Date] <= CurrDate ), Dim_Date[IsWeekday] ),
            SUMX ( SubTable, Dim_Date[IsWeekday] )
        )

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors