Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Prorating Subscription Value between two dates

Hi, 

Sorry if this has been answered thousands of times earlier.

 

From a server I have data of subscriptions extracted to power BI. I have problems dealing with values between two dates (I.e. Start, End dates)

 

E.g.

 

Start: 15.01.21

End: 14.03.21

List Rate: 10

Qty: 10

MRR: 100

 

For each reporting month I want to show this contract's value. Desired output result in something like this

 

January: MRR*(31-15)/31 = 51,612

February: = 100

March = (14/31)*100 = 45,16

 

Now optimally the sum of January and March value should be 100, but the duration of the contract could be a floating point number as well (Start 17.01.21 , End: 03.09.21 for example.

General Comment
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below.

    I tried to create a sample pbix file based on your exlanation.

     

     

    values result =
    VAR startmonthdate =
    MIN ( 'Calendar'[Date] )
    VAR startdatadate =
    SELECTEDVALUE ( Data[Start] )
    VAR endmonthdate =
    MAX ( 'Calendar'[Date] )
    VAR enddatadate =
    SELECTEDVALUE ( Data[End] )
    VAR countdays =
    COUNTROWS ( 'Calendar' )
    VAR mrrvalue =
    SUM ( Data[MRR] )
    VAR result =
    SWITCH (
    TRUE (),
    startdatadate <= startmonthdate
    && enddatadate > endmonthdate, mrrvalue,
    startdatadate <= endmonthdate
    && enddatadate > endmonthdate,
    ( countdays - DAY ( startdatadate ) ) / countdays * mrrvalue,
    startdatadate < startmonthdate
    && enddatadate <= endmonthdate,
    ( DAY ( enddatadate ) / countdays ) * mrrvalue
    )
    RETURN
    result
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below.

    I tried to create a sample pbix file based on your exlanation.

     

     

    values result =
    VAR startmonthdate =
    MIN ( 'Calendar'[Date] )
    VAR startdatadate =
    SELECTEDVALUE ( Data[Start] )
    VAR endmonthdate =
    MAX ( 'Calendar'[Date] )
    VAR enddatadate =
    SELECTEDVALUE ( Data[End] )
    VAR countdays =
    COUNTROWS ( 'Calendar' )
    VAR mrrvalue =
    SUM ( Data[MRR] )
    VAR result =
    SWITCH (
    TRUE (),
    startdatadate <= startmonthdate
    && enddatadate > endmonthdate, mrrvalue,
    startdatadate <= endmonthdate
    && enddatadate > endmonthdate,
    ( countdays - DAY ( startdatadate ) ) / countdays * mrrvalue,
    startdatadate < startmonthdate
    && enddatadate <= endmonthdate,
    ( DAY ( enddatadate ) / countdays ) * mrrvalue
    )
    RETURN
    result
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM