cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mike_true80 Frequent Visitor
Frequent Visitor

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:

 

Task Table.png

 

 

 

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:

 

Month Summary.png

 

 

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

 

Month Chart.pngChart.png

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:

 

Power BI Chart.png

 

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] ) ) )

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Plotting multiple date values on a reference axis

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.

 

q4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Moderator v-qiuyu-msft
Moderator

Re: Plotting multiple date values on a reference axis

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.

 

q4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 287 members 2,924 guests
Please welcome our newest community members: