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