Forum Discussion

Mike_Mace's avatar
Mike_Mace
Resolver I
6 years ago

Multiple Curves from different date

My spreadsheet has 3 dates per deliverable. Planned date, date to Recovery, Actual received date   If I plot each running curve against its own set of dates date all three graphs are ok on an indiv...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

Mike_Mace 

You can connect all three dates to one common date dimension. There will one active and 2 inactive relations. You can activate relation using userelation in the formula

Refer the usages : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • Mike_Mace's avatar
    Mike_Mace
    Resolver I
    6 years ago

    Hi v-kelly-msft & amitchandak 

     

    I have created a Date based on Calendar and marked it as the date in model view.

    I am not able to reach my goals yet 😕

     

    Steps I take:

    1. Create Running Totals for each of the 3x values, Planned, Actual, and Recovery. For the Running Totals I use quick measure calculation; Sum of Planned at Base value and Planned Date at Field
    2. Create Date measure and mark it as the date in model view.
    3. Create Relationships between Date and the date set from each value, Planned Date, Actual Date, Recovery Date (Forecast)
    4. Plot a single line graph with Date (calendar) on Axis and the Running totals to have all three curves against the same date set

    Problem is, when I plot Running (cumulative) against Date (calendar) the Running Planned do not show as cumulative anymore, they show as Planned. Showing in snips.

     

     

    Snip:

    1. Planned info: Table showing Planned date, Planned (per date), Running Totals for Planned
    2. Date alignment: Table shows Planned date, Planned (per date), Date (calendar) aligning neatly with Planned date. This is available only after I create the relationship.
    3. Running info against date misalignment: When I plot Running Total against Date (calendar) the Running totals are actually not running anymore. They go back to Planned (per date).
    4. All 3x Running curves: Each Running column plotted against each own data set. This is what the curves need to look like. These are the curves that I am trying to have on one graph
    5. Planned & Actual Running against Planned Date: This is an experiment. I created a relationship between planned dates and actual dates. Planned curve and Actual curve as per Snip 4. show properly when plotted against their own data sets. Adding Actuals on Planned Curve with Planned dates, shows distorted as you can see. Not the 1, 3, 4 running total. It shows a 2 and 1 In decline.
    6. Raw data: What the data looks like imported from Excel. For every date, a second column calculates 1, or 0 if no date. This is the data I use to calculate the Running Totals.

     

    I am wondering if what I am doing wrong is in calculating the Running Totals. I use the date set of each value. Since I create the relationship between Date (calendar) and the 3x date sets, why does data get distorted on snip 3?

     

     

    1. Planned info2. Date alignment3. Running info against date misalignment4. All 3x Running curves5. Planned & Actual Running against Planned Date6. Raw data

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi Mike_Mace ，

       

      It is  because that you have created a calendar table that linked with 3 differenct date columns in another table,during data filtering ,some data is missing, can you try my way to put the 3 date columns in X-axis?

      If it doesnt work,let me know.

       

      Best Regards,
      Kelly
      Did I answer your question? Mark my post as a solution!
      • Mike_Mace's avatar
        Mike_Mace
        Resolver I
        6 years ago

        Hi v-kelly-msft 

         

        Thanks for the help to drive this to completion.

        Just a note on the snip you send which shows 1 Value, for the sake of clarity, my aim is to get 3 separate curves on the same graph, 3 separate Values.

         

        Sending snips of new attempts:

        7. Test 1: I put all three date-sets on the Axis and all three running-totals in the values on the first graph.

        Planned-running shows properly.

        Actual-running and Recovery-running are distorted. They show the same value, overlapping, and are in decline.

        Actual-running and Recovery-running should have the shape of the curves in the two graphs below.

         

        8. Test 2: Same as Test 1 but I brought Forecast Date _for Recovery) at the top of the list of the date-sets on Axis. This time the Recovery-running shows accurately and Planned and Actual show distorted.

        It appears that that the first date-set on Axis acts like the dominant set for the axis and the other two are dormant.

         

        This is when it got me thinking it would be best to use Date-Calendar for the Axis and link the other 3 date-sets to it. But when I do that the Running-totals do not show as running-totals anymore

        9. Test 3 shows just that.

        Also refer to snip 3. Running info against date misalignment

         

        10. Relationships: This shows the relationships I created

        Date Calendar to Planned Date,

        to Actual Date,

        to Forecast Date

         

        7. Test 18. Test 29. Test 310. Relationships