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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
paulfink
Post Patron
Post Patron

Weekly target not adding up to be the same as my Monthly target

hi guys,

ran into a problem with my weekly target as it is not adding up to my monthly one.

I had to tinker my monthly report so that it would fit into a weekly format.

I did that but the amounts are adding up correctly.

 

here is what I have done:

Weekly Date = 
var CurrentDate=LASTDATE('Calendar Table'[Date])
var DayNumberOfWeek=WEEKDAY(LASTDATE('Calendar Table'[Date]),3)
return
DATEADD(
    CurrentDate,
    -1*DayNumberOfWeek,
    DAY)

This takes the first date of the week and pulls it across the week so that any dates in the date column get picked up as a week.

 

Targets:

Daily Target = '20-21 Revenue Targets'[Target] / MAX('Calendar Table'[Day in Month])

20-21 Weekly Revenue Target = CALCULATE(sum('20-21 Revenue Targets'[Daily Target]), DATESMTD('Closed Calendar Table'[Date]))

20-21 Revenue Weekly Targets = [20-21 Weekly Revenue Target] * [Working days in month]

the working days in month measure is a CALC(SUM of the working days column in my calendar table that shows 1 for workings day and 0 for non-working days.

 

The reason why I am not getting the same amount is that I am dividing a larger amount than I am multiplying. (100 / 30) * 20 = 66.6

what could I change in the formulas that would give me the same as my monthly target? or is there an easier way to do this?

 

 

Desired Output   Sample  
Weekly DateTargetWeekly Target Weekly DateTargetWeekly Target
01/07/202010,0002000 01/07/202010,000966
06/07/2020 2000 06/07/2020 1610
13/07/2020 2000 13/07/2020 1610
20/07/2020 2000 20/07/2020 1610
27/07/2020 2000 27/07/2020 1610
  10,000   7406

 

Weekly Target = (Target / Days in Month) * Working days in Month
Weekly Target = (10,000 / 31) * 3/5
01/07/2020 weekly has 3 days in the week
The rest have 5 days

1 ACCEPTED SOLUTION

@paulfink - This took some work, PBIX file is attached. I believe the core issue is that you were using 5 days weeks but then using a per day of all 31 days in the month. Anyway, this is the calculation (below). Can probably be simplified, I was troubleshooting as I went.

 

Measure 2 = 
    VAR __Date = MAX('Table'[Date])
    VAR __Month = MONTH(MAX([Date]))
    VAR __Year = YEAR(MAX([Date]))
    VAR __Table = SELECTCOLUMNS(FILTER(ALL('Table'[Date]),MONTH([Date]) = __Month && YEAR([Date]) = __Year),"Date",[Date])
    VAR __Table1a = ADDCOLUMNS(__Table,"Month",MONTH([Date]),"Year",YEAR([Date]),"Weeknum",WEEKNUM([Date]))
    VAR __Table1 = ADDCOLUMNS(__Table1a,"Days",COUNTX(FILTER(ALL('Calendar'),WEEKDAY([Date],2) < 6 && MONTH([Date]) = [Month] && YEAR([Date]) = [Year] && WEEKNUM([Date]) = [Weeknum]),[Date]))
    VAR __Table2 = ADDCOLUMNS(__Table1,"Short",5-[Days])
    VAR __Target = MAXX(FILTER('Targets',[Month] = __Month),[Target])
    VAR __DaysInMonth = COUNTX(FILTER(CALENDAR(DATE(__Year,__Month,1),(EOMONTH(DATE(__Year,__Month,1),0))),WEEKDAY([Date],2)<6),[Date])
    VAR __PerDay = __Target / __DaysInMonth
    VAR __Short = SUMX(__Table2,[Short])
    VAR __WeeksShort = COUNTX(FILTER(__Table2,[Days]<5),[Date])
    VAR __Weeks = COUNTX(__Table2,[Date])
    VAR __FullWeeks = __Weeks - __WeeksShort
    VAR __AddPerWeek = SUMX(__Table2,[Short]) / __Weeks * __PerDay
    VAR __Table3 = ADDCOLUMNS(__Table2,"Target",[Days] * __PerDay )
RETURN
    IF(HASONEVALUE('Table'[Date]),MAXX(FILTER(__Table3,[Date] = __Date),[Target]),SUMX(__Table3,[Target]))

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

If that is not it, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler sorry this didnt help

@paulfink - OK, can you post sample data as text and expected output?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler it is already in my post

@paulfink ,

Refer this file

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

 

Daily Target table is created from monthly

Check the date table used has working days, the same way every month add distinct weeks based on week start dates

 

Instead, date, take week start date and month dates  in final table

 

for week start :https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@paulfink - This took some work, PBIX file is attached. I believe the core issue is that you were using 5 days weeks but then using a per day of all 31 days in the month. Anyway, this is the calculation (below). Can probably be simplified, I was troubleshooting as I went.

 

Measure 2 = 
    VAR __Date = MAX('Table'[Date])
    VAR __Month = MONTH(MAX([Date]))
    VAR __Year = YEAR(MAX([Date]))
    VAR __Table = SELECTCOLUMNS(FILTER(ALL('Table'[Date]),MONTH([Date]) = __Month && YEAR([Date]) = __Year),"Date",[Date])
    VAR __Table1a = ADDCOLUMNS(__Table,"Month",MONTH([Date]),"Year",YEAR([Date]),"Weeknum",WEEKNUM([Date]))
    VAR __Table1 = ADDCOLUMNS(__Table1a,"Days",COUNTX(FILTER(ALL('Calendar'),WEEKDAY([Date],2) < 6 && MONTH([Date]) = [Month] && YEAR([Date]) = [Year] && WEEKNUM([Date]) = [Weeknum]),[Date]))
    VAR __Table2 = ADDCOLUMNS(__Table1,"Short",5-[Days])
    VAR __Target = MAXX(FILTER('Targets',[Month] = __Month),[Target])
    VAR __DaysInMonth = COUNTX(FILTER(CALENDAR(DATE(__Year,__Month,1),(EOMONTH(DATE(__Year,__Month,1),0))),WEEKDAY([Date],2)<6),[Date])
    VAR __PerDay = __Target / __DaysInMonth
    VAR __Short = SUMX(__Table2,[Short])
    VAR __WeeksShort = COUNTX(FILTER(__Table2,[Days]<5),[Date])
    VAR __Weeks = COUNTX(__Table2,[Date])
    VAR __FullWeeks = __Weeks - __WeeksShort
    VAR __AddPerWeek = SUMX(__Table2,[Short]) / __Weeks * __PerDay
    VAR __Table3 = ADDCOLUMNS(__Table2,"Target",[Days] * __PerDay )
RETURN
    IF(HASONEVALUE('Table'[Date]),MAXX(FILTER(__Table3,[Date] = __Date),[Target]),SUMX(__Table3,[Target]))

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler this did the job!! thank you so much.

 

I got another challenge if you're up for it, here is the link:

https://community.powerbi.com/t5/Desktop/Calculate-the-change-in-progress-from-the-start-of-last-wee...

@paulfink - If you want people to help, why would you make them re-type all of that data? Doesn't seem very nice.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler that better?

 

Also, i need a column that is like the DAY function (shows 1-31 depending on date) but have it so that it doesn't count days that are not working days e.g.

it goes: 1,2,3,4,5,0,0,6,7,8,9,10,00

not: 1,2,3,4,5,6,7,8,9,10

 

so i can do a MAX formula

amitchandak
Super User
Super User

@paulfink ,Can you share sample data and sample output in table format?

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak i have updated my post with desired output and sample data.

 

Any more questions let me know

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.