Forum Discussion
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 many contracts terminated in a month(end date) when user selects one month.
End Date Null means that contract is still active
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'))
5 Replies
- amitchandak
Super User
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.
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 - Ashish_Mathur
v-frfei-msftCommunity Support
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'))- Ashish_Mathur
You are welcome.
