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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

You need to have a calendar table. And join both dates with that table. One active join one inactive join.

Refer to this example of how to use userelation to choose relations in a formula.

 

https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p/42251

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601