Forum Discussion

Asfaa5's avatar
Asfaa5
Frequent Visitor
6 years ago
Solved

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 Numbe...
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.