Forum Discussion

mike_true80's avatar
mike_true80
Regular Visitor
9 years ago
Solved

Plotting multiple date values on a reference axis

I work with datasets that have multiple date/time values per row for task and activity type tracking.

 

I'm trying to vizualize the data against a reference axis rather than having to chose just one of the columns to be the x-axis.

 

For example, assume I have 10 tasks.  Each has a start date and some of them have an end date:

 

 

 

 

I want to know, for any particular time period, how many tasks have started and how many have finished eg I could summarize by month and calculate a 'to date' value:

 

 

 

And I can chart this type of data easily enough in Excel by building a reference date range to plot against:

 

If I create measures in Power BI and try to plot them I have to select either the Start dates or End dates as the x-axis which results in one of my measures being plotted incorrectly:

 

 

Here, the first task with an End date (#1 ending on 1/5) isn't plotted at 1/5/2017 on the x-axis but at 1/8/2017 which is when the next 'Start' date falls.  Similarly the 2/10/2017 End for #2 doesn't appear at all, and the 2/12/2017 End for #3 appears at 2/21/2017 as total to date = 3.

 

I don't know if I can fix this by just changing my measures, but I figure I need to create some other refernce timeline to act as the x-axis.

 

Measures used:

YTDStart = CALCULATE ( COUNTX ( Tasks , [Start] ) , FILTER ( ALLSELECTED ( Tasks ) , [Start] <= MAX( [Start] ) ) )

YTDEnd = CALCULATE ( COUNTX ( Tasks , [End] ) , FILTER ( ALLSELECTED ( Tasks ) , [End] <= MAX ( [Start] ) ) )

  • Hi mike_true80,

     

    To calculate the count value of Start and End per month, you can create below measure:

     

    StartPerMonth = CALCULATE(COUNTA('Table1'[Start]),FILTER(ALL(Table1), MONTH(Table1[Start])=MAX('calendar'[Month])))

     

    EndPerMonth = CALCULATE(COUNTA('Table1'[End]),FILTER(ALL(Table1), MONTH(Table1[End])=MAX('calendar'[Month])))

     

    To plot a line chart to display count value of Start and End for each day, the measures should be below:

     

    StartPerDay = CALCULATE(COUNTA(Table1[Start]),FILTER(ALL('Table1'),'Table1'[Start]<=MAX('calendar'[Date])))

     

    EndPerDay = CALCULATE(COUNTA(Table1[Start]),ISBLANK('Table1'[End])=FALSE(),FILTER(ALL('Table1'),'Table1'[End]<=MAX('calendar'[Date])))

     

    Please check attached .pbix file.

     

     

    Best Regards,
    Qiuyun Yu

1 Reply

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support

    Hi mike_true80,

     

    To calculate the count value of Start and End per month, you can create below measure:

     

    StartPerMonth = CALCULATE(COUNTA('Table1'[Start]),FILTER(ALL(Table1), MONTH(Table1[Start])=MAX('calendar'[Month])))

     

    EndPerMonth = CALCULATE(COUNTA('Table1'[End]),FILTER(ALL(Table1), MONTH(Table1[End])=MAX('calendar'[Month])))

     

    To plot a line chart to display count value of Start and End for each day, the measures should be below:

     

    StartPerDay = CALCULATE(COUNTA(Table1[Start]),FILTER(ALL('Table1'),'Table1'[Start]<=MAX('calendar'[Date])))

     

    EndPerDay = CALCULATE(COUNTA(Table1[Start]),ISBLANK('Table1'[End])=FALSE(),FILTER(ALL('Table1'),'Table1'[End]<=MAX('calendar'[Date])))

     

    Please check attached .pbix file.

     

     

    Best Regards,
    Qiuyun Yu