cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gfouty
Frequent Visitor

Prorated Daily Budget from Monthly Budget

I need help figuring out the right DAX statement to show a prorated amount based on date filters, YTD, MTD, Daily using the monthly budget data shown in the table below. I also have to take into consideration the number of working days for the month excluding weekends and holidays if there are any for that month.

 

gfouty_0-1635280264436.png

 

Thank you in advance!

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file with steps that are numbered in front of each measure's name.

I only considered weekends because I do not know when are your holidays.

I only calculated Sales Budget, and calculations for GM Budget are the same process.

 

Picture1.png

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file with steps that are numbered in front of each measure's name.

I only considered weekends because I do not know when are your holidays.

I only calculated Sales Budget, and calculations for GM Budget are the same process.

 

Picture1.png

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Hi Jihwan,

 

Thank you for providing solution to my post about prorated budget. I didn't take into consideration the categories so each month's working days is getting counted more than once. Would you be able to get the prorated budget for all of the categories in a month?

 

MonthMO Sales Budget  Profit Budget Category
Jan1 $        1,306,639 $            242,326Category1
Feb2 $        1,217,900 $            225,868Category1
Mar3 $        1,809,389 $            335,564Category1
Apr4 $        1,871,548 $            347,092Category1
May5 $        1,939,097 $            359,620Category1
Jun6 $        2,431,779 $            450,991Category1
Jul7 $        2,457,921 $            455,839Category1
Aug8 $        2,303,864 $            427,268Category1
Sep9 $        1,688,019 $            313,055Category1
Oct10 $        1,706,837 $            316,545Category1
Nov11 $        1,334,696 $            247,529Category1
Dec12 $        1,501,319 $            278,430Category1
Jan1 $            707,206 $            205,965Category2
Feb2 $            705,694 $            205,525Category2
Mar3 $        1,622,676 $            472,584Category2
Apr4 $        1,313,584 $            382,565Category2
May5 $        1,606,444 $            467,857Category2
Jun6 $        1,404,395 $            409,013Category2
Jul7 $        1,116,400 $            325,138Category2
Aug8 $        1,245,360 $            362,695Category2
Sep9 $        1,078,278 $            314,035Category2
Oct10 $            760,724 $            221,551Category2
Nov11 $            771,767 $            224,768Category2
Dec12 $            630,658 $            183,671Category2
Jan1 $        3,527,697 $            792,906Category3
Feb2 $        3,903,140 $            877,293Category3
Mar3 $        6,366,679 $        1,431,013Category3
Apr4 $        6,392,293 $        1,436,770Category3
May5 $        8,669,700 $        1,948,654Category3
Jun6 $        9,036,646 $        2,031,131Category3
Jul7 $        8,208,031 $        1,844,886Category3
Aug8 $        7,586,690 $        1,705,230Category3
Sep9 $        6,551,668 $        1,472,592Category3
Oct10 $        4,759,158 $        1,069,697Category3
Nov11 $        3,910,475 $            878,942Category3
Dec12 $        3,357,914 $            754,745Category3
amitchandak
Super User
Super User

@gfouty , You can refer my blog on the topic

 

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

 

This is for a year, but the approach is to create a table

 

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.