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

Two measures on one graph

Hi 

 

I have a little problem to solve. I have a Dataset which has two date columns one is called create_dt and the other one is close_dt

 

I want to count the numbers of create and closed based on Year and month

 

so my X- Asis should have

 

Year and Month (not from creat_dt or close_dt)

 

My Y axsis should have a bar chat with two bar one for each count

 

 

 

I was able to achieve it with one count but the second count value is incoorect. can someone help me

 

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Two measures on one graph

Hi @Nashy,

Based on my understanding, you should put Year and Month on X-Asix not form  creat_dt or close_dt, so if the start date is 2017/1/2 and the last date is 2017/1/10, so the row should be count when date on X-Asix 2017/1/5, right? If it is, please review the solution.

My sample data.

1.PNG

Create a Calendar table named 'DateTable' not related to my sample date above. Create a measure in Calendar table to get each day.

Selected = CALCULATE(MAX(DateTable[Date]),FILTER(DateTable,DateTable[Date]<=DateTable[Date]))


Create a measure to count the numbers of create and closed based on Year and month.

Count11 = CALCULATE(COUNTROWS(Table3),FILTER(Table3,Table3[Month_Start_Date]<=DateTable[Selected]&&Table3[Month_End_Date]>=DateTable[Selected]))

 

Create a line chart, select the 'DateTable'[Date] as asix, the Count11 measure as value, you will get expected result.

11.PNG

Please let me know if you have any issue.

Best Regards,
Angelia

1 REPLY 1
v-huizhn-msft Super Contributor
Super Contributor

Re: Two measures on one graph

Hi @Nashy,

Based on my understanding, you should put Year and Month on X-Asix not form  creat_dt or close_dt, so if the start date is 2017/1/2 and the last date is 2017/1/10, so the row should be count when date on X-Asix 2017/1/5, right? If it is, please review the solution.

My sample data.

1.PNG

Create a Calendar table named 'DateTable' not related to my sample date above. Create a measure in Calendar table to get each day.

Selected = CALCULATE(MAX(DateTable[Date]),FILTER(DateTable,DateTable[Date]<=DateTable[Date]))


Create a measure to count the numbers of create and closed based on Year and month.

Count11 = CALCULATE(COUNTROWS(Table3),FILTER(Table3,Table3[Month_Start_Date]<=DateTable[Selected]&&Table3[Month_End_Date]>=DateTable[Selected]))

 

Create a line chart, select the 'DateTable'[Date] as asix, the Count11 measure as value, you will get expected result.

11.PNG

Please let me know if you have any issue.

Best Regards,
Angelia