Daily Rate Over Time Calculation and Visual

[Anonymous]

Hi All, 

I trust You are doing well. 

Situation:

I would like to be able to see Daily Rates of people over time (Months) to indentify if the rates are increasing or decreasing with time. 

I'm using a Clustered Column chart for this visualization but I have run in to a problem. 

Visual only represents the Average Daily Rate at the assignment start date as seen below in the case of Arne being selected:

But Arne's assignment, which pays 750 per day runs from September - December. Then, a new assignment that pays 1200 per day starts in January and runs until end of June I would have expected to see something like this:

September  = 750

October = 750

November = 750

December = 750

January = 1200

February = 1200

March = 1200

April = 1200

May = 1200

June = 1200

I would also like to see Daily Rate over time (Months) of the while unit, to understand how overall rates are doing improving or decreasing. 

There are 2 inactive 1-Many relationships enabled from Celndar Table to Assignment Start Date & Assignment End Date.

I would be very grateful for your help. Sample Excel & PBI Files in the link below. 

Sample Files : https://drive.google.com/drive/folders/1UqbvRmqLXIJjqNSO0AxZAm05XR86QsVb?usp=sharing

BR,

Kris

[Anonymous]

Hi,

One of the main issues I noticed in your file was that the data types are not set properly. When you`ll do that, all the date columns will show hierarchy for day, quarter, month and year.

The reason behind this is the calendar table that you`ve created. Rather than having a separate table for the calendar, you can use calculated columns for the calculation you need to do. You`ll get monthly data using the assignment start date as well by using the date hierarchy instead of the date.

Please mark it as a solution if this works for you!!

Regards,

SM

[Anonymous]

Hi SM, 

thanks for your suggestion, unfortunately, this is not fixing the issue that I'm having. 

The Column data types are set correctly. 

I need a dedicated Calendar Table as the real report is using a lot of different Fact Tables that have a Date column. 

Scenario described above is only using one facts table as an example.

I believe that my issue is that the calculation is only using one active relationship and giving me Daily rate information only at the start of the assignment but I need this information for the whole duration of the assigment. 

BR,

Kris

[v-janeyg-msft]

Hi, @Anonymous 

Your needs are not clear, do you only need to set Arne, what about others?

Like:

Also, can you talk about the logic and usage scenarios(use in what visual) of the Daily Rate over time (Months) of the while unit?

If you can show the desired result in visual and calculate logic, we can help you soon.

By the way, it's not easy to show rows that don't exist in the data, and connect two tables with relationships.

Janey