Help pereparing my data for report

[Just2431]

Hello,

I am fairly new to power bi and the most diffciult part for me is to gather and model the data. 

I have 1 table with 2 columns: project name and the budget value.

I have another table 2 with monthly data: e.g Jnauary, project name, and value of time/dollars spent.

What other tables I need to create in order to built an itteractive report with the slicer that will show an updated remaining $. Thank you in advance.

[Anonymous]

Hi, @Just2431 

@lbendlin  is a good answer, if you have a large data span, you may need to create a calendar table. Here's a reference, based on your description, I created the following sample data to try to achieve the effect you need:

table1:

table2:

Relationship:

I use the following DAX expression to find Remaining$:

Remaining$ =
VAR _spent =
    CALCULATE ( SUM ( Sheet6[Spent] ) )
VAR _budget =
    CALCULATE ( SUM ( Sheet5[budget value] ) )
RETURN
    _budget - _spent

I put this measure and the Project name from Table1 into the table visual:

If the above examples can help you, that's 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.

[Just2431]

Thank you! I will follow your guidance and see if it will jave the desired output..I try to create like tracking report, to see how much is already spent each month... Its not a large dataset, i will start from the newest month (i have only two last months of data), It's only maybe 20 rows......I am trying to automate this.. so every time I will drop a new monthly file in the folder, I can only refresh the report and It should update the data. I saw tutorials about creating a data table but most of them shows the calendar for each day but I can create only monthly calendar, correct?

[Anonymous]

Hi, @Just2431 

First of all, appreciate your reply, and at the same time I am very sorry for my late reply. If your calculations require consecutive dates, then creating a calendar table would be a good decision. If you can complete the calculations in the original table, you don't need to create a calendar table. Under normal circumstances, creating a calendar will better help you practice based on the tutorials you browse.

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.

[lbendlin]

You will want to include a calendar table in your data model.