Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Re: Start Date and Date visual

Hi,   I have a table with the contract name, start date and end date. My date field are mm/dd/yyyy. I need to show a visual with count how many new contract came(started date) in a month and how ma...
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    1. Ceate a date table and related it to the fact table.

    date = ADDCOLUMNS(CALENDARAUTO(),"Y Month",FORMAT([Date],"YYYY-MMM"))

     

    2. After that, to create two measures as below to get the excepted result.

    end = CALCULATE(COUNTROWS('Table'),USERELATIONSHIP('Table'[End Date],'date'[Date]))
    Start = CALCULATE(COUNTROWS('Table'))

     

     

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Thank you. Your solution was easy to create and understood clearly.

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

1. Ceate a date table and related it to the fact table.

date = ADDCOLUMNS(CALENDARAUTO(),"Y Month",FORMAT([Date],"YYYY-MMM"))

 

2. After that, to create two measures as below to get the excepted result.

end = CALCULATE(COUNTROWS('Table'),USERELATIONSHIP('Table'[End Date],'date'[Date]))
Start = CALCULATE(COUNTROWS('Table'))

 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you. Your solution was easy to create and understood clearly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    You are welcome.