cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Planned vs Actual Data - Matrix and Bar chart, with Planned and Actual Dates Respectively

So I have Budgeted cost which is coming from Planned dates i.e. Finish date and then Earned(Acutal) Cost coming from the Actual Finish dates. 

 

Earned Cost and Budgeted cost are different values with their own dates but needs to be shown on same axis. Earned is coming from Actual dates and Budgeted cost is coming from Finish (Planned Dates).  How to show them on same axis or in a same chart.

Both of them are in the same table.

 

I created a Monthly bins using Planned dates and plotted the Budgeted cost on it. But when I put the Earned Cost on the same graph it is showing Earned Cost same as Budgeted cost (4.42Mil and 3.43Mil) which is not correct Earned cost on that month. The earned cost should show its value based on its own Acutal dates, that is where it comes from. 

Bar Chart Link

 

Matrix Chart Link

 

Budget and Earned  this is actually the asnwer , Earned in month of August is actually 2.2M Aug and 5.17M in Sept.

 

This is the  Data  in the table

 

 

So the Item Month Bin is created from Finish Dates (Planned Finish Dates), Acutal Finish bin is created from Acutal Finish Dates

 

If this is possible to do without Bin that would be okay, I used bins to have the Summarized data as per the "MMYY" in format of Aug2019, Sept2019 and so on)

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Planned vs Actual Data - Matrix and Bar chart, with Planned and Actual Dates Respectively

Hi @Zahid_shaikh22 ,
I suppose that you to create a new table to display distinct date firstly and then you need to create two measures to compute the sum in different fields.
You can see the formulas and the sample result below.

New Table: 
Calender = DISTINCT(UNION(DISTINCT(Cost[ActualFinish(bins)]),DISTINCT(Cost[Month Bin])))
Measures: 
BudgetMeasure =
CALCULATE (
    SUM ( Cost[BudgetedCost] ),
    FILTER (
        ALLSELECTED ( Cost ),
        'Cost'[Month Bin] IN FILTERS ( 'Calender'[dates] )
    )
)
Actual =
CALCULATE (
    SUM ( Cost[EarnedValueCost] ),
    FILTER (
        ALLSELECTED ( Cost ),
        'Cost'[ActualFinish(bins)] IN FILTERS ( 'Calender'[dates] )
    )
)

1.png

Best Regards,
Jack Chen

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Planned vs Actual Data - Matrix and Bar chart, with Planned and Actual Dates Respectively

Hi @Zahid_shaikh22 ,
I suppose that you to create a new table to display distinct date firstly and then you need to create two measures to compute the sum in different fields.
You can see the formulas and the sample result below.

New Table: 
Calender = DISTINCT(UNION(DISTINCT(Cost[ActualFinish(bins)]),DISTINCT(Cost[Month Bin])))
Measures: 
BudgetMeasure =
CALCULATE (
    SUM ( Cost[BudgetedCost] ),
    FILTER (
        ALLSELECTED ( Cost ),
        'Cost'[Month Bin] IN FILTERS ( 'Calender'[dates] )
    )
)
Actual =
CALCULATE (
    SUM ( Cost[EarnedValueCost] ),
    FILTER (
        ALLSELECTED ( Cost ),
        'Cost'[ActualFinish(bins)] IN FILTERS ( 'Calender'[dates] )
    )
)

1.png

Best Regards,
Jack Chen

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Planned vs Actual Data - Matrix and Bar chart, with Planned and Actual Dates Respectively

Hi Jack Chen  @v-shex-msft  ,

 

Thank you so much for the reply. 

 

Below is the correct values, I used your formula and it works really great for a bar chart,  

 

Corrected PvA.jpg

 

 

But when the data is plotted in  a Table it is showing all the valus as same for all, I think it is because of the relationship between the calendar and data table.

 

Values.jpg

 

 

How to make relationship between the "Task" Table and "Calendar" Table. 

 

Do i need to make a copy of the "Task" table or make copy of "Calendar " Table ? 

Relationships RVA.jpg

 

I did try to make relationship between the Task and calendar, but I am getting the following error;

 

I also tried to different realtionhip type from ManytoMany , OnetoMany, etc.  but the error remains the same.

Relationship error.jpg

 

I have uploaded the pbix file here if you want to see, https://we.tl/t-mCAed3cTWj

 

Many Thanks

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors