Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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've only been able to get this: the 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.
Solved! Go to 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.
Hi v-steven-msft,
That worked incredibly! Thank you very much!
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.
Hi v-steven-msft,
That worked incredibly! Thank you very much!
@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.
@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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
74 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |