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 ...
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

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