Saving daily "Count"

[smithshire]

I have a visual that displays the daily count of the total number of open contracts. Is there a way of storing this value each day in a table? 

[Samarth_18]

Hi @smithshire ,

I think you can't save it anywhere. But few workaround as below:-

1. Stored this data in DB.

2. or Create a seperate measure which calculate the total count for yesterday like today()-1.

Thanks,

Samarth

[smithshire]

Will this store each daily value?

[v-rzhou-msft · Accepted solution]

Hi @smithshire ,

The calculation is based on your data model. I think there should be a contract table with the open date and close date for your contracts.

Create a DimDate table and then create a measure to calculate the daily count of the total number of open contracts.

DimDate = CALENDARAUTO()

Measure:

Daily count of the total number of open contracts = 
CALCULATE (
    COUNT ( 'Table'[Contracts] ),
    FILTER (
        'Table',
        'Table'[Open Date] <= MAX ( DimDate[Date] )
            && 'Table'[Close Date] >= MAX ( DimDate[Date] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[smithshire]

Hi @v-rzhou-msft 

That works great apart from where we have contracts that are still open that do not have an closed date. How would I amend the measure to include these in the count?

Regards

[truptis]

@smithshire , Table a table visual, put Date field and your measure. It will give you the count.

You can create a measure too for counting contracts: Count_of_Contracts = Count(columnname of open contracts)