Forum Discussion

Just2431's avatar
Just2431
Icon for Helper I rankHelper I
2 years ago

Help pereparing my data for report

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 wit...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Just2431
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.