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

Date relationship and cumulative figures

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.

 

image.png

 

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. 🙂

 

 

image.png

 

 

 

image.png

1 ACCEPTED SOLUTION
dan_yoxall
Helper I
Helper I

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])
)
)

 

 

View solution in original post

4 REPLIES 4
dan_yoxall
Helper I
Helper I

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])
)
)

 

 

View solution in original post

spuder
Resolver IV
Resolver IV

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

Hi Spuder, did you see my response?  

 

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

 

Thanks

Dan

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].

 

 

Image 001.jpg

 

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].

 

Image 002.jpg

 

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.  

 

 

Image 003.jpg

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

 

 image.png

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors