Reducing a value from a column by week in a DAX table

[ColetonC]

I am trying to show a reduciton in a projects budget each week by subtracting the weekly billed amount from the original budget amount. I currently have a summary table pulling in the weekly billed amount by project here:

The contract budget is from a separate table all other columns are from the same table. 

I am trying to show the new available budget each week by subtracting the billed amount. My desired outcome is here:

I am having trouble determing the correct DAX to make this result happen. 

[Ashish_Mathur]

Hi,

Why are you creating a DAX table?  Why are you not solving it with a measure?  Do you have a Date column?  If you are OK with a measure solution and have a Date column, then share the download link of the PBI file. 

[ColetonC]

I did try a measure initially but had trouble geting a reduction by week to be illustrated in a visual. There is a date column in the table with containing the expense data. (I have a calendar table that is connected to the dates in the expense table.) There is not a date column in the table containing the project data (budget). Here is the measure I tried 

[Ashish_Mathur]

As requested earlier, share some data to work with and show the expected result.

[ColetonC]

Here is the result I am looking for 

I have a PowerBI file I can now share but I am not able to share in this reply. What is the best way to share the PBIX file?

[Ashish_Mathur]

Upload the file to Google Drive/One Drive and share the download link.

[ColetonC]

https://drive.google.com/file/d/1vFSqLZ7XtvNwdYOtP4hTttOkgJSeESEW/view?usp=drive_link

[Anonymous]

Hi, @ColetonC 

I'm happy to answer your questions. You can modify your formula according to the DAX formula I provide below. Then add a calculated column to find Remianning Budgett:

DAX formula:

Test Project Billed = 
var T= SUMMARIZE('Employee Time',[Project#],[Calendar Week],"Dollars Billed",SUM('Employee Time'[Dollars Billed]),"Hours Billed",SUM('Employee Time'[Billable Hours Worked]),"Contract Budget",MAX('Project List'[Contact Budget Amount]))
RETURN ADDCOLUMNS(T,"index",ROWNUMBER(T))

I added an index column to the DAX formula, mainly to assist calculations. The generated summary table is as follows:

You calculate Remianning Budgett using the DAX formula below:

DAX formula:

Remainning Budgett = 
var _Total = MAX('Test Project Billed'[Contract Budget])
var _R_Total = CALCULATE(SUM('Test Project Billed'[Dollars Billed]),FILTER('Test Project Billed','Test Project Billed'[index]<=EARLIER('Test Project Billed'[index])))
RETURN _Total-_R_Total

The cumulative difference is achieved through the Index column and the EARLIER function. The results are as follows:

I have provided the sample PBIX file used this time below. The above are my thoughts on Remaining Budgett. If it can help you, that would be great.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.