Forum Discussion

Nashy's avatar
Nashy
Regular Visitor
9 years ago
Solved

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

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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.



    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.



    Please let me know if you have any issue.

    Best Regards,
    Angelia

1 Reply

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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.



    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.



    Please let me know if you have any issue.

    Best Regards,
    Angelia