topic Re: Total Calculation based on Total/Day for given Month in DAX Commands and Tips

Total Calculation based on Total/Day for given Month

troyhimes — Mon, 21 Feb 2022 01:12:09 GMT

Can't seem to work this one out....

I have a table that has budget amount per day for a given month with a date value in the format month/1/year per Area. Data below is for 'Area 1'

Using the per/day rate I'd like to calculate the total budget across any time period broken out by Area. The measure below of course works for a single month across any Area, but understandably as soon as I filter across multiple months it fails.

The correct value should be 16124.58=(503.23 * 31 days in Jan.) + (524.45 * 1 day in Feb).

How do I accomplish this?

Thanks!

Re: Total Calculation based on Total/Day for given Month

amitchandak — Mon, 21 Feb 2022 03:01:44 GMT

troyhimes , Not very clear

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-Convert-to/ba-p/1657798

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

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

Re: Total Calculation based on Total/Day for given Month

troyhimes — Mon, 21 Feb 2022 05:38:46 GMT

amitchandak You're right, I wasn't very clear! I forgot to include the "measure below".

I scanned your linked post and will check it out closely in the morning. Prior to the measure above, I've determined the value per day using a technique similar to yours, but maybe the piece that I'm missing in the measure is the 'TotalMTD' for my day count(?)

Re: Total Calculation based on Total/Day for given Month

amitchandak — Mon, 21 Feb 2022 06:36:36 GMT

troyhimes .
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

because TotalMTD blog I shared. I need to try with data

Re: Total Calculation based on Total/Day for given Month

troyhimes — Mon, 21 Feb 2022 13:59:53 GMT

amitchandak

PowerBI Question.pbix

Here it is. The measure in question is 'MMP_Tons'. The plan per day for the given month is 'MMP_Tons/Day' and the date I've associated with that plan is always the 1st of the month. For a given time period I want to calculate the planned tons based on the plan per day.

Re: Total Calculation based on Total/Day for given Month

tamerj1 — Tue, 22 Feb 2022 07:05:36 GMT

Hi troyhimes
Here is your file with the solution https://www.dropbox.com/t/lZdUKnpNp5E6wAXA
It is actually simple. you just need to iterate over the table with right granularity which is in this case, the 'Calendar' table

MMP_Tons = VAR SumMMPTonDay = SUM (Actuals_and_MonthlyPlans[MMP_Tons/Day] ) VAR Result = SUMX ( 'Calendar', SumMMPTonDay ) RETURN Result

Please let me know if this answers you query. Thank you

Re: Total Calculation based on Total/Day for given Month

troyhimes — Tue, 22 Feb 2022 14:37:52 GMT

Hi tamerj1 ,

Thanks for your attempt, but it looks like this yields the same result as my original measure....

Examples:

1. Date Filter=1/1/22 to 1/31/22, MMP_Tons/Day = 503.23, MMP_Tons = 15,600.01 (503.23 *31) CORRECT

2. Date Filter=2/1/22 to 2/28/22, MMP_Tons/Day = 524.45, MMP_Tons = 14,684.59 (524.45 * 28) CORRECT

3. Date Filter = 1/1/22 to 2/1/22, MMP_Tons = 32,885.62 (1027.68 * 32) INCORRECT

CORRECT VALUE = 16,124.58 = (503.23 * 31) + (524.45 * 1)

Re: Total Calculation based on Total/Day for given Month

tamerj1 — Wed, 23 Feb 2022 04:42:35 GMT

troyhimes
I Plead guilty 🙂
You are absolutely right. I overlooked the data and did not pay enough attention. I did not notice that the Tons/Day values are only in the first day while in other days are blank which is actually your original problem.
Please refer to updated file with the solution https://www.dropbox.com/t/RYcaBXfL2RcdQd5S
The code is little long. I know it can be shorter and more simple by using GENERATE function but I personally still did not learn how to use it properly. Also can be solved by adding a new calculated column. Pretty sure other methods are available out there. However this one seems to work

MMP_Tons = VAR Table1 = SUMMARIZE ( Actuals_and_MonthlyPlans, Actuals_and_MonthlyPlans[Area], 'Calendar'[YYMM], "@Tons", SUM ( Actuals_and_MonthlyPlans[MMP_Tons/Day]) ) VAR Table2 = SELECTCOLUMNS ( Table1, "@YYMM", 'Calendar'[YYMM], "@@Tons", [@Tons] ) VAR Table3 = CROSSJOIN ( 'Calendar', Table2 ) VAR Table4 = FILTER ( Table3, [YYMM] = [@YYMM] ) VAR Result = SUMX ( Table4, [@@Tons] ) RETURN Result

Please let me know if this solves your problem. Have a nice day!

Re: Total Calculation based on Total/Day for given Month

troyhimes — Sat, 26 Feb 2022 16:48:18 GMT

Thanks tamerj1 , much appreciated, that did the trick.