Two lines, one chart

[tlh]

I'd pull my hair out if I had any left.....

I'm trying to have two lines on one chart. One line is expected burndown. The other is actual burndown. This is to display how we've used our budget over time vs how we should be using it. 

I have my expense ledger:

I have a daily date table:

And I have my budget table:

When I graph my expenses over time, it looks good. 

When I graph the budget over time, looks marvelous

When I put them together, nothing happens:

If I shift it into secondary values, same result. 

When I use the Date Table for the axis, I get this:

It seems that the dates in the date table can't/won't match up in the expense ledger. I do have them formatted the same way. 

Please help.

TLH

[Anonymous]

Could it be that you have a Date/Time field somewhere? If there is a time component, then it won't relate to a date column. It would be great if you can share a sample pbix so that we can dig further into it.

[tlh]

Hello,

I checked and they are all date data type, however I did notice something interesting. I am not able to put this data on a regular table. I believe its because PowerBI doesn't recognize the expense date as the date data-type. 

When I put it on a report, it does not offer me the hierarchy. 

Is there something else that needs to happen to make this a date datatype?

I'm trying to sanitize it and then I'll upload. 

[Jihwan_Kim]

Hi, @tlh 

I am not sure if I understood your question correctly, but when putting the budget into the visualization, please try to create the measure like below in order to allocate amounts correctly per day.

Please check the below picture and the sample pbix file's link down below. It shows the measure that is for creating the allocation measure to know day-by-day-budget amount. Then, the granularity will be the same as the actual expense table, and those two data can be put into the same visualization.

Budget Total =

SUM(Budget[Amount])

Budget Total Daily Allocation =

VAR dayscount =

CALCULATE (

COUNTROWS ( Dates ),

FILTER ( ALL ( Dates ), Dates[Year] = MAX ( Dates[Year] ) )

) - 1

VAR dailyamount = [Budget Total] / dayscount

VAR dailycumulate =

SUMX (

FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) ),

[Budget Total] / dayscount

) - dailyamount

RETURN

[Budget Total] - dailycumulate

https://www.dropbox.com/s/d1u3fgkmi431qsx/tlh.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[tlh]

Hi Jihwan,

Thanks for sharing this. How was the date table created? I can't see the formulas. 

TLH

[Jihwan_Kim]

Hi, @tlh 

Thanks for the feedback.

I usually create dim-date table in Power Query Editor.

You can go into the Power Query Editor and can see the table. It is not imported but it was directly created inside the power query editor.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM