Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Correctly Summing Min Max for visual

I am working on a measure and when I check the totals the data is not adding up to the total box.

my calculations should return -583k and in the table it shows that but the row coulum totals and other visuals i have show -413k so essentially missing (or adding 170k). I have narrowed it down to the Min max functions i am using and know that the 170k is coming from customer 5 in the data set as they are in a positive number. I am confident the measure plan in my code is correct as it lines up with my calculations in Excel. I beleive the issue is when i am using Min and Max. Thoughts and help?

 

MTH Plan = 
VAR TRACKING_START =
     MAX(Calendar_Table[PRJ_TRACK_DATE])
VAR EOM = 
    MAX(Calendar_Table[EOM])
VAR DAY_IN_MONTH =
    DAY(EOM)
VAR DAY_INITATIVE = 
    MAX(0,MIN(EOM-TRACKING_START,DAY_IN_MONTH))
VAR DAY_TRACKING =  
    MIN(0,DIVIDE(DAY_INITATIVE,7)*[Plan])
RETURN
    if(DAY_TRACKING>0,0,DAY_TRACKING)

 

 

Tracking_Start: is when I start tracking that Customer

EOM: Selected EOM

DAY_IN_MONTH: Just the total days in selected month

Day Initative: how many days are being tracked in that month. So if i start tracking a customer in that current month it would show how many active days for the selected month. So in Customer 3 they would be null for September and 30 days for the month of November

Day_tracking: The days per week 

Month Plan is the monthly plan based on the Day Tracking

CUSTOMERTRACKING_STARTEOMDAY_IN_MONTHDAY_INITATIVEDAY_TRACKINGMONTH PLAN
Customer 111/13/202110/31/202231314.430
Customer 22/26/202210/31/202231314.43-17000
Customer 310/1/202210/31/202231304.290
Customer 42/26/202210/31/202231314.43-253000
Customer 54/30/202210/31/202231314.430
Customer 64/9/202210/31/202231314.430
Customer 72/12/202210/31/202231314.43-184000
Customer 89/17/202210/31/202231314.43-129000

1 Reply