Reverse cumulative from today to future

[Anonymous]

Hi,

So I have a data table connected to a PostGresSQL DB with multiple customers' contracts, product serial number and their final expiration date. The contracts may cover multiple products, so I'm using the serial number as key.

If I use a distinctcount on the serial number and filter the expiration date from today forward, I get how many products have active contracts today. I wanted to have a column chart which would get this total and decrease it monthly according to how many contracts are expiring that month. Every month, the dashboard would be updated to account for new/renewed contracts, so this total would be dynamic and not hard coded.

Basically what I need is this: a stacked column chart with the decreasing active contracts and the monthly expirations. Every month the total would change as new contracts are being signed or renewed, as well as the monhtly expirations.I want something like this: decreasing total stacked with monthly expirations

I've only been able to get this: the monthly expirations.

I've only been able to generate this: monthly expirations

PS.: I did these charts in Excel, but I wanted in PBI because of the dashboard capability and also because there are other information there as well.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

I created a calendar table to display the month and year.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 6, 1 ), DATE ( 2022, 2, 28 ) ),
    "MonthYear",
        FORMAT ( [Date], "MMM" ) & "-"
            & RIGHT ( YEAR ( [Date] ), 2 ),
    "Sort", YEAR ( [Date] ) & FORMAT ( MONTH ( [Date] ), "0#" )
)

About Expiring Contracts, you can try this measure.

Expiring Contracts =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[product serial number] ),
    FILTER ( 'Table', [final expiration date] IN VALUES ( 'Calendar'[Date] ) )
)

About Active Contracts, you can try this measure.

Active Contracts =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[product serial number] ),
    FILTER ( 'Table', [final expiration date] > MAX ( 'Calendar'[Date] ) )
)

The result is as follows.

You can check more details from here.

Best Regards,

Stephen Tao

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

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

I created a calendar table to display the month and year.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 6, 1 ), DATE ( 2022, 2, 28 ) ),
    "MonthYear",
        FORMAT ( [Date], "MMM" ) & "-"
            & RIGHT ( YEAR ( [Date] ), 2 ),
    "Sort", YEAR ( [Date] ) & FORMAT ( MONTH ( [Date] ), "0#" )
)

About Expiring Contracts, you can try this measure.

Expiring Contracts =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[product serial number] ),
    FILTER ( 'Table', [final expiration date] IN VALUES ( 'Calendar'[Date] ) )
)

About Active Contracts, you can try this measure.

Active Contracts =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[product serial number] ),
    FILTER ( 'Table', [final expiration date] > MAX ( 'Calendar'[Date] ) )
)

The result is as follows.

You can check more details from here.

Best Regards,

Stephen Tao

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

[Anonymous · Accepted solution]

Hi v-steven-msft,

That worked incredibly! Thank you very much!

[Greg_Deckler]

@Anonymous If I understand correctly this seems like Periodic Revenue - https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@Anonymous , if you have date. then you can try a formula like this with date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] >=minx(date,date[date])))