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. Sta...
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

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