Calculate a Running Total Against a Static Amount by Date Period

[KayceVC]

I have a table that shows the amount of project revenue gained during a period of time defined by filters on the report. The purpose is to see, at a glance, how much revenue was recognized by project by period. When looking at current dates, this works as expected comparing the total of accepted time entries vs the project budget, but when look back at previous weeks (using the date filters) it ignores the time entered previously and only calculates the time entered during that week. This can cause it to show revenue being recognized when the project budget may have already been met or exceeded.

The filters causing me issues are my Dates[Week] and Months[Month]. 

My measure at present is:

SUMX(
 	'Projects',
	SWITCH(Projects[PM_Billing_Method_ID],
        "N", 
        CALCULATE(
            IF(CALCULATE(SUM('Time Entries'[Billable_Amt]),ALL(Dates[week]),ALL(Months[Month])) <= SUM(Projects[Billing_Amount]),
			CALCULATE(SUM('Time Entries'[Billable_Amt]),ALL(Dates[week]), ALL(Months[Month]),
                'Time Entries'[Included In Billable Hours],
                USERELATIONSHIP('Project Tickets'[TicketNbr], 'Time Entries'[SR_Service_RecID])
            ),
            CALCULATE(SUM(Projects[Billing_Amount]))
		    )
	    )
    )
)

I have tried changing the filters in the measure from ALL to MAX, but that throws an error.  Our reports are generally fairly simple in BI and more complex in the SQL (which I am far more experienced with than DAX), so trying to manipulate data in BI I have not done as much with. My end goal would be for the table to compare the sum of TimeEntries[Billable_Amt] (up to max allowed date) against the Project[Billing_Amount].

If no filters and SUM(TimeEntries[Billable_Amt]) > Project[Billing_Amount] then Billing_Amt else SUM(TimeEntries[Billable_Amt]).

If filtered by week or month and and SUM(TimeEntries[Billable_Amt]) (selected period and earlier) less SUM(TimeEntries[Billable_Amt] (selected period)) > Project[Billing_Amount] then 0 else Project[Billing_Amount] - SUM(TimeEntries[Billable_Amt]) (selected period and earlier) + SUM(TimeEntries[Billable_Amt]) (selected period)

Ex:

budget (Billing_Amount) = $40,000

current revenue (SUM(TimeEntries[Billable_Amt]) (selected period) = $15,000

all revenue to current(SUM(TimeEntries[Billable_Amt]) (selected period and earlier) = $50,000

all revenue before current(SUM(TimeEntries[Billable_Amt]) (before period) = $35,000

So the total time entries ($50k) is greater than the budget ($40k), but the total time entries less current ($35k) is not, so there is revenue left from current ($5k) to recognize, but not all of it. I am not sure how to write these measures if it is not allowing me to use the MAX function against the dates. With the current measure, since $15k is less than $40k, it will recognize it all, even though that's not actually accurate.

I hope I've explained this well enough to get any help available. If someone can just point me in the right direction to correct my errors, it would be greatly appreciated

Table Relationships Involved:

Dates[Month] *:1 Month[Month]

TimeEntries[Date] *:1 Dates[Date]

TimeEntries[PM_Project_RecID] *:1 Projects[PM_Project_RecID]

[KayceVC · Accepted solution]

Thank you for the help and suggestions, I was ultimately able to resolve this issue.

test4 = 
VAR MaxDate = MAX(Dates[Date])
VAR MinDate = MIN(Dates[Date])
Var MaxTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], ALL(Dates))
Var PeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(Dates, Dates[Date] <= MaxDate && Dates[Date] >= MinDate))
Var ThroughPeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] <= MaxDate))
Var SincePeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] > MaxDate))
Var BeforePeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] < MinDate))
VAR BillingAmount = SUM(Projects[Billing_Amount])
RETURN
IF(ThroughPeriodTime < SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), PeriodTime, 
    IF(BeforePeriodTime < SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), BillingAmount-BeforePeriodTime, 
        IF(ThroughPeriodTime < SUM(Projects[Billing_Amount]), PeriodTime,
            IF(BeforePeriodTime < SUM(Projects[Billing_Amount]), BillingAmount-BeforePeriodTime,
                IF(BeforePeriodTime > SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), 0,
                    IF(BeforePeriodTime > SUM(Projects[Billing_Amount]), BillingAmount,0
                    )
                )
            )
        )
    )    
)

[v-yuaj-msft]

Hi @KayceVC ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Yuna

[v-yuaj-msft]

Hi @KayceVC ,

Could you please share your sample files and the expected result to have a clear understanding of your question? I can do some tests for you.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

Best Regards,

Yuna