How to get the total number of active contracts up to a date?

[marianammartins]

Hello,

I need help with DAX for the following problem:

In sum I want to know how many active contracts I have up to a certain date (state = active ou cutted). The data is in an archive table, so everytime there's a change in the contract, that table is updated (PK is Contract No and Version No).

I've tried a lot of ways, but most of them don't fully work in one way or another and I'm already lost in everything I've tried.

Here is an example of the data and the logic I was following to write the formulas

Thanks!

[v-jianboli-msft · Accepted solution]

Hi @marianammartins ,

Please try:

Active||Cutted' = 
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&OR([State]="Active",[State]="Cutted")))
return _c

Finished' = 
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Finished"))
return _c

Pre' = 
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Pre"))
return _c

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @marianammartins ,

Please try:

First, create a new table:

Up to Date = ADDCOLUMNS(FILTER(CALENDAR(EOMONTH(MIN('New Table'[Archive Date]),0),EOMONTH(MAX('New Table'[Archive Date]),0)),[Date]=EOMONTH([Date],0)),"Up To Date","up to "&FORMAT([Date],"DD MMM YY"))

Output:

Then create a table visual:

Pre' = 
var _a = MAX('Up to Date'[Date])
var _b = FILTER('New Table',[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Pre"))+0
return _c

Finished' = 
var _a = MAX('Up to Date'[Date])
var _b = FILTER('New Table',[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Finished"))+0
return _c

Active||Cutted' = var _a = MAX('Up to Date'[Date])
var _b = FILTER('New Table',[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&OR([State]="Active",[State]="Cutted")))+0
return _c

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marianammartins]

That works a bit better thanks, but if I put the measure in the y-axis of a bar chart and my calendar table in the x-axis, it doesn't return the desired results

 

[v-jianboli-msft]

Hi @marianammartins ,

You need to put the 'Up to Date'[Date] in the X-axis: 

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marianammartins]

I'm using the Calendar (DimDate) as the "Up to Date" because the goal is to know the number of active contracts at any given date

[v-jianboli-msft]

Hi @marianammartins ,

Please try:

Unpivot the columns:

Output:

Then apply the measure:

Finished = COUNTROWS(FILTER('Table',[State]="Finished"&&[Value]<>BLANK()))+0

Pre = COUNTROWS(FILTER('Table',[State]="Pre"&&[Value]<>BLANK()))+0

Active||Cutted = COUNTROWS(FILTER('Table',OR([State]="Active",[State]="Cutted")&&[Value]<>BLANK()))+0

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marianammartins]

Hello

Thank you so much for trying to help and I'm sorry I didn't explain myself correctly. The only columns that are part of the table are the ContractNo, State and Archive Date... the other ones are only examples of the logic I was following, I did that to try and breakdown the steps I would need to take into to account to achieve the correct results