Multiple measures on single chart with multiple dates on X axis

[arb1782]

Hi

I've got multiple measures I want to put on one time series graph. On the page I want to set this chart up, I also have a date slicer set up with months as big buttons. The months are sourced from a date table which links to the columns entitled 'xxxx month'  (see below) and the measures have the relevant relationship specified with USERELATIONSHIP.

Measure 1 = Number of records created, plotted by record creation date (week commencing), using default relationship Month (date table) to Record creation month

Measure 2 = Number of record reviews, plotted by record review date (week commencing), using relationship Month (date table) to Record reviewed month

Measure 3 = Number of closed records, plotted by record closed date (week commencing), using default relationship Month (date table) to Record closed month

How might I go about putting these all on 1 graph but using the separate week commencing fields on the x axis?

Dummy data here:

Index	Record creation date	Record creation week commencing	Record creation month	Record reviewed date	Record reviewed week commencing	Record reviewed month	Record closed date	Record closed week commencing	Record closed month
1	01/06/2020	01/06/2020	June	08/06/2020	08/06/2020	June	15/06/2020	15/06/2020	June
2	02/06/2020	01/06/2020	June	09/06/2020	08/06/2020	June	16/06/2020	15/06/2020	June
3	03/06/2020	01/06/2020	June	10/06/2020	08/06/2020	June	17/06/2020	15/06/2020	June
4	04/06/2020	01/06/2020	June	11/06/2020	08/06/2020	June
5	05/06/2020	01/06/2020	June
6	09/07/2020	06/07/2020	July	13/07/2020	13/07/2020	July	20/07/2020	20/07/2020	July
7	10/07/2020	06/07/2020	July
8	11/07/2020	06/07/2020	July	15/07/2020	13/07/2020	July
9	12/07/2020	06/07/2020	July	16/07/2020	13/07/2020	July

Thanks

arb

[v-kelly-msft]

Hi @arb1782 ，

Create 3 measures as below:

Measure 1 = CALCULATE(COUNT('Table'[Record creation week commencing]),FILTER(ALL('Table'),'Table'[Record creation month]=SELECTEDVALUE('Table 2'[Month])))

Measure 2 = CALCULATE(COUNT('Table'[Record reviewed week commencing]),FILTER(ALL('Table'),'Table'[Record reviewed month]=SELECTEDVALUE('Table 2'[Month])))

Measure 3 = CALCULATE(COUNT('Table'[Record closed week commencing]),FILTER(ALL('Table'),'Table'[Record closed month]=SELECTEDVALUE('Table 2'[Month])))

And you will see:

Check the .pbix file attached.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[arb1782]

Thanks for the advice so far. I need to get the x axis to be 'week commencing' and for the measures to plot on that axis using data from three different 'week commencing' fields. I've played with your PBI file Kelly but it doesn't seem to solve my problem.

arb

[v-kelly-msft]

Hi  @arb1782 ,

Can you show me your expected output?Which "week commencing " do you wanna use?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[arb1782]

Hi @v-kelly-msft

I'm looking for all 3 'week commencing' fields to be plotted on the same axis. If I do this, the measures only plot against one of the fields, meaning that 1 measure is accurate and the 2 others are inaccurate.

The date that would be on the visual would be:

	Number of recorded created	Number of records reviewed	Number of records closed
01/06/2020	5
06/07/2020	4
08/06/2020		4
15/06/2020			3
13/07/2020		3
20/07/2020			1

Unfortunately I can't manipulate the tables in PowerQuery because my week commencing fields are calculated fields.

arb

[amitchandak]

@arb1782 , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

For week in calendar you can refer :

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...