Date relationship and cumulative figures

[dan_yoxall]

Hi

I'm trying to plot on a graph of cumulative hours from two sources; planned hours and actual (booked) hours.  

Referring the below snapshot, I create a date key (yyyymmdd) in both sets of data.  I then created a date table.  PowerBI autodetected the relationship and then I manually amended the relationship to both rather than single direction.  

Have I set this up correctly?  If my understanding of the termenolgy is correct, this is a star schema with a dimension table (Date Table) at the centre and linked via branches to fact tables - Project Hours Booked (i.e. booked hours) and Programme Resource (i.e. planned hours) - however the fact table should be at the centre of the star schema.  Is that correct?  

I couldn't think of another way to create a relationship between the two fact tables.

I have then tried to create a line graph of the data.  Note the hours here have been converted to value (£) using a measure.

Referring to the graphs,

              Bottom left is Planned hours (cumulative) vs Date Heirarchy (see snapshot further below)

              Bottom right is Booked hours (cumulative) vs Date Heirarchy (see snapshot further below)

Note that in both instances, the Date Heirarchy has been created using the dates within the fact tables and not the combined dimension date table.  Both graphs are returning cumulative totals.

             Top graph is Plan hours (cumulative) and Booked hours (cumulative) vs Date Heirarchy

This time, the date heirarchy has been created using the dates in the dimension table (Date Table).  

For some reason the green line (Planned spend) is cumulative which is correct but the black line is not.  I have used the same measure in both instances so I don't understanding why it is suddenly not work for one.  This is the measure I have used:

Actual Spend (cumulative) =
                      CALCULATE(
                                 SUMX ( 'Project Hours Booked', 'Project Hours Booked'[Cost, Reg.]) * MAX('Fee Factor'[Fee Factor]),
                                         FILTER(
                                                   ALL('Project Hours Booked'[Date]),
                                                   'Project Hours Booked'[Date] <= MAX('Project Hours Booked'[Date])
)
)

Sorry for the long message but any ideas why this isn't working?  I don't know if it is because of the filter which is referencing the dates in the original fact table whilst the x-axis is based upon the dates in the dimension table.  The truth is I don't know how I would alter the measure to test this.  

Thanks in advance. 🙂

[dan_yoxall · Accepted solution]

Turns out there was a slight difference between the two formulae.  Hence why one measure was giving cumulative totals and one wasn't.  I amended the Acutal Spend to the following:

Actual Spend (cumulative) =
                      CALCULATE(
                                 SUMX ( 'Project Hours Booked', 'Project Hours Booked'[Cost, Reg.]) * MAX('Fee Factor'[Fee Factor]),
                                         FILTER(
                                                   ALL('Project Hours Booked'[Date]),
                                                   'Project Hours Booked'[Date] <= MAX('Project Hours Booked'[Date])
)
)

[spuder]

Hi @dan_yoxall

At the moment I can't see what your problem really is.

First of all in your measure you only use data from Project Hours Booked. Why did you activate the relationship between datetable and Programme Ressource... ?

Then I wonder what result you get and what result you expected.

Can you turn of the graph and show it as a table. It is a little bit easier for the understanding to see figures.

Can you say in human words what you want to calculate step by step?

Greetings spuder

[dan_yoxall]

Hi Spuder, did you see my response?  

Did it make sense?  Any ideas what the issue is?

Thanks

Dan

[dan_yoxall]

Hi

Thanks for responding and sorry that I wasn't very clear.  I have created a couple of visuals which hopefully better explain what I have created.

The first graph is based upon data from the table 'Programme Resource Planning'.  The x-axis being a heirarchy of the [Date] and [Week No].

The second graph is based upon data from the table 'Project Hours Booked'.  The x-axis is again a heirarchy of the [Date] and [Week No].

I am trying to show the y-axis values 'Programme Resource Planning'[Planned Spend (cumulative 2] and 'Project Hours Booked'[Actual Spend (cumulative)] on a single graph.  See below annotated relationship diagram.  Hopefully this explains why I have created the relationship.  

I am expecting the combined graph to display the same information as individual graphs; cumulative totals.  

 

The issue I am having is that whilst 'Programme Resource Planning'[Planned Spend (cumulative 2] is displayed as a cumulative value in the combined graph, 'Project Hours Booked'[Actual Spend (cumulative)] is not cumulative.  This is despite the fomulas for the two measures using the same functions and structure.  

Any ideas what I am doing wrong?

Thanks again.  Dan