cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Reverse cumulative from today to future

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 expirationsI 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 expirationsI'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.

2 ACCEPTED SOLUTIONS
Microsoft
Microsoft

Hi @gcorazza ,

 

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#" )
)

5.png

 

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.

6.png

 

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.

View solution in original post

Hi v-steven-msft,

That worked incredibly! Thank you very much!

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

Hi @gcorazza ,

 

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#" )
)

5.png

 

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.

6.png

 

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.

View solution in original post

Hi v-steven-msft,

That worked incredibly! Thank you very much!

View solution in original post

Super User IV
Super User IV

@gcorazza 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.

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

@gcorazza , 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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors