Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FN
Regular Visitor

Time Intelligence Measures with dynamic Time Periods

Hello together,

 

I am working on a Power BI report and wasn't able to find a solution for my problem so far.

 

I want to calculate a so called Cancellation Rate for my company, meaning how many contracts were active at the beginning of a period (e.g. 01/01/2015) and how many of these contracts were cancelled in a certain period (e.g. from 01/01/2015 until 12/31/2015) and then calculating the percentage.

Therefore I am importing all contracts from the CRM into Power Bi. Every Contract has a Contract Start Date and a Contract End Date.

 

I used to calculate this in Excel with
=SUMIFS(Revenue; Contract Start Date < DateX; Contract End Date > DateX)

to calculate how many contracts were active on a certain DateX, e.g. 01/01/2015.

 

Do you have any recommendations on how I could solve this in Power BI?

My goal would be to have all the different periods in a chart on the axis and the according percentage as the value.

I would be so thankful if someone here could help me.

Regards,
Florian

9 REPLIES 9
v-huizhn-msft
Employee
Employee

Hi @FN,

Based on my understanding, you can transfer the formula to DAX, which is used to create a measure.

active amount=CALCULATE(SUM(Table[Revenue]),Filter(Table, AND(Table[contract start data]<DateX[Date],Table[contract end data]>DateX[Date])))


Then create a measure to calculate the amount of concelled contracts.

 

concelled amount=CALCULATE(SUM(Table[Revenue]),Filter(Table, Table[contract end data]>=DateX[Date1],Table[contract end data]<=DateX[Date2])))


Then calculate the percentage.

percentage=[active amount]/[concelled amount]


Then you can create a line chart, then select the Datex[Date] as axis, the [percentage] as value level, you will get expected result.

If this is not want you want, please post the sample data as @Phil_Seamark suggested.

Best Regards,
Angelia

Hi Angelia,

 

I posted some sample data minutes ago.
I already tried it like in your suggestion, but as the coloumns Dates['Period Dates'] and Contracts['Contract Start Date'] are in different tables, I can't refere to both in one measure unfortunately, but I can do so in an extra column. I don't know why this is the case or what I am doing wrong here.

 

This is the column I have:

 

4.PNG

Do you know why this doesn't work as a measure?

In the analysis part I then want to split this rate up into different contract types, regions, etc. As long as the rate is just an extra column it is not working unfortunately.

 

Thank you,

Florian

Phil_Seamark
Employee
Employee

Hi @FN,

 

Any chance you can post a sample set of data, including a mock up of what you might expect once you have everything working?

 

Cheers,

 

Phil


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

@v-huizhn-msft

 

I don't know if you receive a notification when I didn't mark you in my replying post.

If you received a notification, then I am sorry for this post. 🙂

 

Thank you so much for your help,

Florian

Hi @FN,

 

Any chance you can share some sample data in text form rather than as an image to save lots of key-ing in 🙂

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

here is some sample data for you. Thank you so much already. I really appreciate it.

 

CustomerContract TypeTotal PriceContract Start DateContract End DateAddress 1: Country (Customer)
Customer 1Update Contract1.687,448.1.20057.31.2017Austria
Customer 2Update Contract2.595,006.1.20035.31.2017United States
Customer 3Support Contract6.923,043.1.20169.30.2020Germany
Customer 4Update Contract1.119,9611.1.200610.31.2017Germany
Customer 5Support Contract4.560,007.30.20108.31.2014United States
Customer 6Cloud Agreement1.932,0010.1.20159.30.2016Spain
Customer 7Support Contract7.856,284.1.20153.31.2018United Kingdom
Customer 8Update Contract798,721.1.200112.31.2005Switzerland
Customer 9Cloud Agreement1.890,0012.1.201611.30.2017United States
Customer 10Update Contract309,001.1.201112.31.2014Isle Of Man
Customer 11Cloud Agreement343,008.15.20143.31.2017Germany
Customer 12Update Contract6.319,445.1.20024.30.2017Germany
Customer 13Update Contract4.535,1610.1.20069.30.2017Germany
Customer 14Cloud Agreement4.116,0010.1.20159.30.2017Germany
Customer 15Support Contract6.996,007.7.20118.31.2017United States
Customer 16Support Contract193,801.1.201112.31.2017Switzerland
Customer 17Update Contract232,928.1.20117.31.2017Germany
Customer 18Support Contract329,762.1.20081.31.2018Germany
Customer 19Support Contract1.644,009.1.20078.31.2008United States
Customer 20Support Contract312,007.17.20097.31.2016United States
Customer 21Support Contract2.814,0011.26.201411.30.2016Mexico
Customer 22Support Contract744,003.16.20113.31.2012Mexico
Customer 23Update Contract1.610,645.1.20024.30.2013Germany
Customer 24Update Contract1.545,2412.1.201111.30.2017Germany
Customer 25Update Contract5.355,007.30.20108.31.2017United States
Customer 26Support Contract200,002.18.19982.18.2002United States
Customer 27Update Contract1.771,203.1.200512.31.2017Germany
Customer 28Support Contract4.902,0012.31.200810.31.2017United States
Customer 29Update Contract1.279,566.1.201512.31.2017Austria
Customer 30Support Contract312,009.30.20089.30.2014United States
Customer 31Support Contract2.719,801.1.19802.28.2018United States
Customer 32Support Contract4.999,9212.10.200312.31.2017United States
Customer 33Support Contract312,006.24.20136.30.2014United States
Customer 34Support Contract32.325,009.1.20158.31.2020United States
Customer 35Cloud Agreement4.116,006.1.20165.31.2017Germany
Customer 36Support Contract8.397,0010.19.20116.30.2017Brazil
Customer 37Update Contract1.437,845.1.20154.30.2017Switzerland
Customer 38Update Contract6.624,249.1.20078.31.2017Germany
Customer 39Support Contract3.153,1211.1.201012.31.2017Germany

Hi @FN

 

There are a few moving parts to this one so rather than type it up, here is a PBIX file for you to have a play with

 

https://wgtnpowerbi-my.sharepoint.com/personal/phil_wgtnpowerbi_onmicrosoft_com/_layouts/15/guestacc...

 

I think it is close, but once we have applied any tweaks, we can post more detail in here for future searches.

 

Feel free to ask any questions you have 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark;

 

it took me a while to try to apply this format to the whole data set from our company.

Your solution looks really good, it is definitely going in the right direction. Thank you so much for your help already!

I only do not understand, how you created this "Processed Data" Table.
I can see that you typed in the following formula there, but I don't know where to type this formula in, when I create a new Power BI sheet with the whole data from the company. Did you do "Merge Queries" or did you start the new table with "Enter new Data"? Or is there a completely different way you did this? Once I know how to create this Processed Data Tab, I think it should be working.

 

Processed Data = ADDCOLUMNS(
     FILTER(
            CROSSJOIN('Raw Data';Quarters);
                    (
                    -- Started before start of Quarter and was active during the quarter
                    [Contract Start Date]<=[QuarterStart] &&
                    [Contract End Date] > [QuarterStart]
                    )
                ||
                    (
                    -- Started During the Quarter
                    [Contract Start Date]<=[QuarterEnd] &&
                    [Contract Start Date]>=[QuarterStart]  
                    )
                ) ;
               
                "Contracts Started" ; if([Contract Start Date]<[QuarterEnd] && [Contract Start Date]>=[QuarterStart];1;0);
                "Contracts Ended" ; if([Contract End Date]<[QuarterEnd] && [Contract End Date]>=[QuarterStart];1;0)
               
                )


I am really looking forward hearing from you.

Cheers,
Florian

Hi Phil,

 

Yes, here I have some sample data:

 

So this is my model with the realtionships:

Unbenannt.PNG

 

Account = All the Customers with its information regarding the customer's adress, country, etc.

Contract = All Contracts with information like Contract Start Date, Contract End Date, etc (matched to Account with the Customer ID)

Subterritories = More detailled analysis for regions, so I match a subterritory (like UK) from the Customer's account to the firm of the company, Regions, etc. (not really important for the problem here)

Exchange Rate = As we have contracts in many countries, I am matching the currency from contract to Exchange rate (also not really important for this problem)

Dates = I don't know if this is necessary. It is a date table, with dates in it. As I want to measure the different things (Active Revenue, Cancelled Revenue) on a quarterly period, so I have all the dates of the start of a quarter period there, e.g. 01/01/2015, 04/01/2015, 07/01/2015 and so on. Then I am referring to this date when I am calculating how much revenue was valid at a certain date. Also the same with expired contracts. So far everything clear?

 

2.PNG

 

So at the moment, I am calculating the revenue that is valid like this:

=CALCULATE(SUM(Revenue); FILTER(Contracts, Contract Start Date < Dates.QuarterBeginnDate); FILTER(Contracts, Contract End Date > Dates.QuarterBeginnDate)

It is an extra column, because when I try to calculate is as a measure, the 'Dates.QuarterBeginnDate' is not possible to select in the formula from above. I don't know the reason.

Same for the expired contracts:

=CALCULATE(SUM(Revenue);FILTER(Contracts, Contract Start Date < Dates.QuarterBeginnDate); FILTER(Contract End Date > Dates.QuarterBeginnDate); FILTER(Contract End Date > Dates.Period End Date)

 

Like this, I am calculating a percentage. Also here, it is again not possible to create a measure, because these column names do not appear when I try to enter it in the formula. So I created an extra column again.

 

What I would love to have is something like this:3.PNG

 

So to calculate a rate for every period. This is how I did it in Excel and then I graph it in a chart.

 

What I want in Power BI is basically the same, to calculate this (only for help) and then graph the rates in a chart. What I want is that with Slicer in Power BI I can easily re calculate the rates for different contract types, for different countries, for different Sales Partner. I don't know in this case if Power BI with its time intelligence is intelligent enough to aleady calculate the rates for periods itself or if I need these date tables or maybe something different.

Do you have any idea of how I could do that?


Thank you and Regards,
Florian

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.