Forum Discussion

tlh's avatar
tlh
Frequent Visitor
5 years ago

Two lines, one chart

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

Need Help

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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
     
     
     

    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's avatar
      tlh
      Frequent Visitor
      5 years ago

      Hi Jihwan,

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

      TLH

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      tlh
      Frequent Visitor
      5 years ago

      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.