Forum Discussion
Active Contracts Count by Year/Month
Hello Team,
im new to Power Bi. I need a help on Dax Measure calculation of Active Contracts Count by Year/Month.
I have a Table "Sales" with 7,65,000 data. Sales Table contains "Policy Number", "Contract Start Date", "Contract End Date".
Need output as below :
| Year | Month | Active Count |
| 2019 | Jan | 4000 |
| Feb | 4500 | |
| Mar | 5000 | |
| Apr | 5500 | |
| May | 6000 | |
| Jun | 6500 | |
| Jul | 7000 | |
| Aug | 7500 | |
| Sep | 8000 | |
| Oct | 8500 | |
| Nov | 9000 | |
| Dec | 9500 | |
| 2020 | Jan | 9800 |
| Feb | 10100 | |
| Mar | 10000 | |
| Apr | 11000 | |
| May | 12000 | |
| Jun | 11500 | |
| Jul | 10000 | |
| Aug | 9000 | |
| Sep | 7500 | |
| Oct | 7000 | |
| Nov | 6500 | |
| Dec | 6000 | |
| 2021 | Jan | 5500 |
| Feb | 5000 | |
| Mar | 4500 | |
| Apr | 4000 | |
| May | 3500 | |
| Jun | 3000 | |
| Jul | 2500 | |
| Aug | 2000 | |
| Sep | 1500 | |
| Oct | 1000 | |
| Nov | 100 | |
| Dec | 10 |
Can anyone help me on this.
Hi Asfaa5
Create one date table.
Date=Calender(DATE (2010, 1, 1), DATE (2020, 12, 31)).
Then create year and month column in the date table.
Then create measure suggested by Anonymous .
And add year, month and measure to your table visual.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
4 Replies
What is the required logic for active contracts?
Is it where end date in blank() or 12/31/9999?
New measure=Calculate(Count[Table[Contract]),Filter(table,Table[end_Date]='12/31/9999')
Please modify filter condition as per your active contracts logic.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.Hi. Thanks for your revert.
Logic is for Showing How many Contract are going to be active in Jan-20, Feb-20, Mar-20, like this till Contract Expire completely.
There is no blank in Contract End Date. Last date is till 31/12/2027.
Usually in cases like this the best is to create a separate date table contaning all dates covering the time period you want to analyze. The table should not be connected to the data table with any relations.
Then you create a measure something like this:
Active Contracts = CALCULATE( COUNTROWS('Sales'), FILTER('Sales', 'Sales'[Contract Start Date] <= MAX('Dates'[Date]) && 'Sales'[Contract End Date] >= MIN('Dates'[Date])) )I have assumed each row has one contract, so COUNTROWS('Sales') is the number of contracts.
Finally, use year / month / date from the dates table in combination with the new measure.Hi Asfaa5
Create one date table.
Date=Calender(DATE (2010, 1, 1), DATE (2020, 12, 31)).
Then create year and month column in the date table.
Then create measure suggested by Anonymous .
And add year, month and measure to your table visual.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
