Forum Discussion
Multiple dates within same record on visual
Newbie alert; I've only just started to use PBI so will happily be pointed at tutorials etc. that I haven't yet found.
I have a single data source that has timestamp (dd/mm/yyy hh:mm:ss) fields that store when certain actions are taken e..g. [TSStart], [TSTriage], [TSDocument], [TSEnd],. I've grouped each of these by month so have [TSStart (bins)] etc. Each record has a unique ID [Reference].
What I would like to do in the first instance is show on the same visual, say a line and column chart, a count of [Reference] for how many processes started in a month as the column and how many ended in that same month as the line.
TIA
Hi,
In the Query Editor, change the data type to Date. Create a Calendar Table and then create an active relationship (Many to One and Single) from the TSStart date column of the Data Table to the Date column of the Calendar Table. Create another relationship [Inactive] (Many to One and Single) from the TSEnd date column of the Data Table to the Date column of the Calendar Table. Create calculated column formulas for extracting Year, Month name and Month number. Sort the Month name column by the Month number. To your visual, drag Year and Month name column from the Calendar Table. Write these measures:
ID_start = distinctcount(Data[TSStart])
ID_end = calculate([ID_start],userelationship(Data[TSEnd],Calendar[Date]))
Hope this helps.
3 Replies
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523I'll bear this in mind next time
Hi,
In the Query Editor, change the data type to Date. Create a Calendar Table and then create an active relationship (Many to One and Single) from the TSStart date column of the Data Table to the Date column of the Calendar Table. Create another relationship [Inactive] (Many to One and Single) from the TSEnd date column of the Data Table to the Date column of the Calendar Table. Create calculated column formulas for extracting Year, Month name and Month number. Sort the Month name column by the Month number. To your visual, drag Year and Month name column from the Calendar Table. Write these measures:
ID_start = distinctcount(Data[TSStart])
ID_end = calculate([ID_start],userelationship(Data[TSEnd],Calendar[Date]))
Hope this helps.
