Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Calculate Products used during the first 12 months for new business

  • 1.  Calculate Products used during the first 12 months for new business

    Posted 26 days ago
    Hello everyone,

    I'm working with two datasets, one which stores customer usage information ( account name, usernames, type of products used, date of purchase) and one which stores account information ( account name, type of product, yearly renewal date, date of purchase). The customers pay for their bundle of software products on a yearly basis.

    I would like to calculate the product quantities used during the first 6 or 12 months by companies.

    For example:

    Company A - New Business 1yr contract started in February - How many products did they buy/use during the first 6 months
    Company B - New Business 1yr contract started in October - How many products did they buy/use during the first 6 months

    How can I calculate on the same table or chart the products used by

    Company A - February - March - April - May - June - July
    Company B - October November - December - January - February - March

    Thanks,
    Mark

    ------------------------------
    Mark R
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Calculate Products used during the first 12 months for new business

    Top Contributor
    Posted 25 days ago
    Hi Mark,

    Can you please post a sample of your model ?

    How are the companies contract start date stored?

    ------------------------------
    Vincent L.
    Chartered accountant - Expert-comptable
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Calculate Products used during the first 12 months for new business

    Posted 25 days ago
    Hi Vincent,

    Thank you for your reply.

    The contract start date is stored in the Forecast Opp SF (Close Date) and the Usage Report contains the individual products used by users and companies (Completion Date).

    Thanks

    Model Screenhot


    ------------------------------
    Mark Rock
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Calculate Products used during the first 12 months for new business

    Silver Contributor
    Posted 25 days ago
    Hi Mark,
    If you want to calculate total quantities for both the companies in single table then use datesinperiod dax for 6 month each.
    or if you want use different table then create two table and use visual level filter by month.

    ------------------------------
    Krishna Nandan
    SME
    Bangalore
    ------------------------------

    Academy - Online Interactive Learning from Experts