Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
DANIPAR
Frequent Visitor

Splitting monthly target into Daily targets with weekday % in Live connection

‎01-28-2025 12:39 AM

Hello,

 

Currently we have monthly targets, but I want to split them into daily ones to track results during the month.

 

I want them split based on proportion of each day of the week:

Monday - 18%

Tuesday - 18%

Wednesday - 16%

Thursday - 16%

Friday - 17%

Saturday - 8%

Sunday - 7%

 

Meaning that formula should evaluate how many different week days are in the month and divide monthly target accordingly.

 

Complication being that I have live connection to data model/analysis services and I cannot create tables. Just measures.

 

DANIPAR_0-1738053486229.png

Maybe someone can help?

 

Thanks!

Labels:
Message 1 of 8
266 Views
3 ACCEPTED SOLUTIONS
Bibiano_Geraldo
Super User
Super User

‎01-28-2025 01:26 AM

Hi @DANIPAR ,

Please try to adapt this logic to attend your need:

Daily Target = 
VAR CurrentDate = MAX('Calendar'[Date])  -- Adjust to your date column
VAR MonthlyTarget = [Monthly Target]  -- Replace with your actual measure for monthly target
VAR StartOfMonth = STARTOFMONTH('Calendar'[Date])
VAR EndOfMonth = ENDOFMONTH('Calendar'[Date])

-- Weekday counts in the current month
VAR Mondays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 1), 1)
VAR Tuesdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 2), 1)
VAR Wednesdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 3), 1)
VAR Thursdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 4), 1)
VAR Fridays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 5), 1)
VAR Saturdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 6), 1)
VAR Sundays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 7), 1)

-- Weekday proportions
VAR MondayTarget = 0.18 * MonthlyTarget / Mondays
VAR TuesdayTarget = 0.18 * MonthlyTarget / Tuesdays
VAR WednesdayTarget = 0.16 * MonthlyTarget / Wednesdays
VAR ThursdayTarget = 0.16 * MonthlyTarget / Thursdays
VAR FridayTarget = 0.17 * MonthlyTarget / Fridays
VAR SaturdayTarget = 0.08 * MonthlyTarget / Saturdays
VAR SundayTarget = 0.07 * MonthlyTarget / Sundays

-- Assign daily target based on the weekday
RETURN 
SWITCH(
    WEEKDAY(CurrentDate, 2),
    1, MondayTarget,
    2, TuesdayTarget,
    3, WednesdayTarget,
    4, ThursdayTarget,
    5, FridayTarget,
    6, SaturdayTarget,
    7, SundayTarget
)

View solution in original post

Message 4 of 8
227 Views
0
Bibiano_Geraldo
Super User
Super User
In response to DANIPAR

‎01-28-2025 02:31 AM

Hi @DANIPAR ,

In this case, just calculate the first day of the month using STARTOFMONTH('Calendar'[Date]), The MonthlyTarget variable is now calculated using CALCULATE([Monthly Target], 'Calendar'[Date] = FirstDayOfMonth), which ensures the target used is from the first day of the month, regardless of the current date in the report.

The rest of the logic remains the same as you can see bellow:

Daily Target = 
VAR CurrentDate = MAX('Calendar'[Date])  -- Adjust to your date column
VAR FirstDayOfMonth = STARTOFMONTH('Calendar'[Date])  -- First day of the month
VAR MonthlyTarget = CALCULATE([Monthly Target], 'Calendar'[Date] = FirstDayOfMonth)  -- Get monthly target from first day of the month
VAR StartOfMonth = FirstDayOfMonth
VAR EndOfMonth = ENDOFMONTH('Calendar'[Date])

-- Weekday counts in the current month
VAR Mondays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 1), 1)
VAR Tuesdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 2), 1)
VAR Wednesdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 3), 1)
VAR Thursdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 4), 1)
VAR Fridays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 5), 1)
VAR Saturdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 6), 1)
VAR Sundays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 7), 1)

-- Weekday proportions
VAR MondayTarget = 0.18 * MonthlyTarget / Mondays
VAR TuesdayTarget = 0.18 * MonthlyTarget / Tuesdays
VAR WednesdayTarget = 0.16 * MonthlyTarget / Wednesdays
VAR ThursdayTarget = 0.16 * MonthlyTarget / Thursdays
VAR FridayTarget = 0.17 * MonthlyTarget / Fridays
VAR SaturdayTarget = 0.08 * MonthlyTarget / Saturdays
VAR SundayTarget = 0.07 * MonthlyTarget / Sundays

-- Assign daily target based on the weekday
RETURN 
SWITCH(
    WEEKDAY(CurrentDate, 2),
    1, MondayTarget,
    2, TuesdayTarget,
    3, WednesdayTarget,
    4, ThursdayTarget,
    5, FridayTarget,
    6, SaturdayTarget,
    7, SundayTarget
)

View solution in original post

Message 6 of 8
207 Views
Bibiano_Geraldo
Super User
Super User
In response to DANIPAR

‎02-12-2025 08:53 AM

Hi @DANIPAR ,

To have get comulative quantity use this DAX:

MTD Running Target = 
VAR CurrentDate = LASTDATE('Calendar'[Date])
RETURN
SUMX(
    FILTER(
        ALLSELECTED('Calendar'),
        'Calendar'[Date] <= CurrentDate &&
        'Calendar'[Date] >= STARTOFMONTH(CurrentDate)
    ),
    [Quantity]
)

 

View solution in original post

Message 8 of 8
139 Views
0
7 REPLIES 7
Bibiano_Geraldo
Super User
Super User

‎01-28-2025 01:26 AM

Hi @DANIPAR ,

Please try to adapt this logic to attend your need:

Daily Target = 
VAR CurrentDate = MAX('Calendar'[Date])  -- Adjust to your date column
VAR MonthlyTarget = [Monthly Target]  -- Replace with your actual measure for monthly target
VAR StartOfMonth = STARTOFMONTH('Calendar'[Date])
VAR EndOfMonth = ENDOFMONTH('Calendar'[Date])

-- Weekday counts in the current month
VAR Mondays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 1), 1)
VAR Tuesdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 2), 1)
VAR Wednesdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 3), 1)
VAR Thursdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 4), 1)
VAR Fridays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 5), 1)
VAR Saturdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 6), 1)
VAR Sundays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 7), 1)

-- Weekday proportions
VAR MondayTarget = 0.18 * MonthlyTarget / Mondays
VAR TuesdayTarget = 0.18 * MonthlyTarget / Tuesdays
VAR WednesdayTarget = 0.16 * MonthlyTarget / Wednesdays
VAR ThursdayTarget = 0.16 * MonthlyTarget / Thursdays
VAR FridayTarget = 0.17 * MonthlyTarget / Fridays
VAR SaturdayTarget = 0.08 * MonthlyTarget / Saturdays
VAR SundayTarget = 0.07 * MonthlyTarget / Sundays

-- Assign daily target based on the weekday
RETURN 
SWITCH(
    WEEKDAY(CurrentDate, 2),
    1, MondayTarget,
    2, TuesdayTarget,
    3, WednesdayTarget,
    4, ThursdayTarget,
    5, FridayTarget,
    6, SaturdayTarget,
    7, SundayTarget
)
Message 4 of 8
228 Views
0
DANIPAR
Frequent Visitor
In response to Bibiano_Geraldo

‎01-28-2025 02:23 AM

This works really well! Only thing being that it seems that targets in our data model are stored on the first day of the month... How your code can be adapted to overcome this? 

DANIPAR_0-1738059819243.png

 

Message 5 of 8
212 Views
Bibiano_Geraldo
Super User
Super User
In response to DANIPAR

‎01-28-2025 02:31 AM

Hi @DANIPAR ,

In this case, just calculate the first day of the month using STARTOFMONTH('Calendar'[Date]), The MonthlyTarget variable is now calculated using CALCULATE([Monthly Target], 'Calendar'[Date] = FirstDayOfMonth), which ensures the target used is from the first day of the month, regardless of the current date in the report.

The rest of the logic remains the same as you can see bellow:

Daily Target = 
VAR CurrentDate = MAX('Calendar'[Date])  -- Adjust to your date column
VAR FirstDayOfMonth = STARTOFMONTH('Calendar'[Date])  -- First day of the month
VAR MonthlyTarget = CALCULATE([Monthly Target], 'Calendar'[Date] = FirstDayOfMonth)  -- Get monthly target from first day of the month
VAR StartOfMonth = FirstDayOfMonth
VAR EndOfMonth = ENDOFMONTH('Calendar'[Date])

-- Weekday counts in the current month
VAR Mondays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 1), 1)
VAR Tuesdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 2), 1)
VAR Wednesdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 3), 1)
VAR Thursdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 4), 1)
VAR Fridays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 5), 1)
VAR Saturdays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 6), 1)
VAR Sundays = COUNTX(FILTER(ALL('Calendar'), 'Calendar'[Date] >= StartOfMonth && 'Calendar'[Date] <= EndOfMonth && WEEKDAY('Calendar'[Date], 2) = 7), 1)

-- Weekday proportions
VAR MondayTarget = 0.18 * MonthlyTarget / Mondays
VAR TuesdayTarget = 0.18 * MonthlyTarget / Tuesdays
VAR WednesdayTarget = 0.16 * MonthlyTarget / Wednesdays
VAR ThursdayTarget = 0.16 * MonthlyTarget / Thursdays
VAR FridayTarget = 0.17 * MonthlyTarget / Fridays
VAR SaturdayTarget = 0.08 * MonthlyTarget / Saturdays
VAR SundayTarget = 0.07 * MonthlyTarget / Sundays

-- Assign daily target based on the weekday
RETURN 
SWITCH(
    WEEKDAY(CurrentDate, 2),
    1, MondayTarget,
    2, TuesdayTarget,
    3, WednesdayTarget,
    4, ThursdayTarget,
    5, FridayTarget,
    6, SaturdayTarget,
    7, SundayTarget
)
Message 6 of 8
208 Views
DANIPAR
Frequent Visitor
In response to Bibiano_Geraldo

‎02-07-2025 06:08 AM

Hello @Bibiano_Geraldo , Now I'm trying to make month-do-date running SUM of those Daily targets. I used very simple formula for actual volumes (CALCULATE([Quantity],DATESMTD([Date])), but this doesn't work with those daily targets. Tried with SUM and SUMX, but just getting daily targets, but not month-do-date sum:

DANIPAR_1-1738937563463.png

 

 

Message 7 of 8
172 Views
Bibiano_Geraldo
Super User
Super User
In response to DANIPAR

‎02-12-2025 08:53 AM

Hi @DANIPAR ,

To have get comulative quantity use this DAX:

MTD Running Target = 
VAR CurrentDate = LASTDATE('Calendar'[Date])
RETURN
SUMX(
    FILTER(
        ALLSELECTED('Calendar'),
        'Calendar'[Date] <= CurrentDate &&
        'Calendar'[Date] >= STARTOFMONTH(CurrentDate)
    ),
    [Quantity]
)

 

Message 8 of 8
140 Views
0
Fowmy
Super User
Super User

‎01-28-2025 01:06 AM

@DANIPAR 

I did a webinar sometime back on this subject and covered many scenarios including this case on budget allocation. Please watch: https://youtu.be/E2MOnVEa99s

Did I answer your question? Mark my post as a solution! and hit thumbs up


⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

Message 3 of 8
238 Views
0
bhanu_gautam
Super User
Super User

‎01-28-2025 12:59 AM

@DANIPAR , Try using create a measure for the total number of each weekday in the current month:

 

DAX
WeekdayCount =
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR DaysInMonth = DAY(EOMONTH(TODAY(), 0))
VAR Weekdays =
ADDCOLUMNS(
CALENDAR(DATE(CurrentYear, CurrentMonth, 1), DATE(CurrentYear, CurrentMonth, DaysInMonth)),
"Weekday", WEEKDAY([Date], 2)
)
RETURN
SUMX(
Weekdays,
SWITCH(
TRUE(),
[Weekday] = 1, 1,
[Weekday] = 2, 1,
[Weekday] = 3, 1,
[Weekday] = 4, 1,
[Weekday] = 5, 1,
[Weekday] = 6, 1,
[Weekday] = 7, 1,
0
)
)

 

Create a measure for the daily target based on the weekday:

DailyTarget =
VAR MonthlyTarget = [YourMonthlyTargetMeasure]
VAR WeekdayPercent =
SWITCH(
TRUE(),
WEEKDAY(TODAY(), 2) = 1, 0.18,
WEEKDAY(TODAY(), 2) = 2, 0.18,
WEEKDAY(TODAY(), 2) = 3, 0.16,
WEEKDAY(TODAY(), 2) = 4, 0.16,
WEEKDAY(TODAY(), 2) = 5, 0.17,
WEEKDAY(TODAY(), 2) = 6, 0.08,
WEEKDAY(TODAY(), 2) = 7, 0.07
)
VAR TotalWeekdays = [WeekdayCount]
RETURN
MonthlyTarget * WeekdayPercent / TotalWeekdays

 

Use the DailyTarget measure in your visuals to track daily progress.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 2 of 8
243 Views
0

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All