Forum Discussion

kiranbrao's avatar
kiranbrao
New Member
9 years ago
Solved

Visualization for contract start/end dates and cost

I have several entries, each with the fields: organization name, contract start date, contract end date, and contract amount. I am trying to create a visual that will display organization contracts a...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi kiranbrao,

     

    You can create a calendar table:

     

    Calendar = CALENDAR(MIN('Table1'[Contract start date]),MAX('Table1'[Contract end date]))

     

    Then create a measure in fact table:

     

    Amount = CALCULATE(SUM('Table1'[Contract amount]),FILTER('Table1','Table1'[Contract start date]<=MAX('Calendar'[Date]) && 'Table1'[Contract end date]>=MAX('Calendar'[Date])))

     

     

     

    Please check attached .pbix file.

     

    Best Regards,
    Qiuyun Yu

     

     

     

Shelley's avatar
Shelley
Icon for Post Prodigy rankPost Prodigy
6 years ago

v-qiuyu-msft  This is really helpful. Thank you! I plotted my contract values over time, but now is there a way to plot actual transactions as the run up to the total contract value, and then start over at the new contract? My formulas are giving me the blue and red line, but I want the blue and orange line.

 

BLUE Line:

Entitlement Amount =
CALCULATE(SUM('Entitlements'[Entitlement Cap]),
FILTER('QBContract','QBContract'[SAP Contract Start] <= MAX('RA_Daily_Calendar'[Date]) &&
'QBContract'[SAP Contract End] >= MAX('RA_Daily_Calendar'[Date])))

 

RED Line:
List Price Amount =
CALCULATE(
    SUM('Transaction'[List_Price]),
    FILTER(
        ALLSELECTED('RA_Daily_Calendar'[CalendarYear-Mo]),
        ISONORAFTER('RA_Daily_Calendar'[CalendarYear-Mo], MAX('RA_Daily_Calendar'[CalendarYear-Mo]), DESC)
    )
)

 

 

 

I also tried this, but this really didn't work:

Amount List Price =
CALCULATE(SUM('Transaction'[List_Price]),
FILTER('QBContract','QBContract'[SAP Contract Start] <= MAX('RA_Daily_Calendar'[Date]) &&
'QBContract'[SAP Contract End] >= MAX('RA_Daily_Calendar'[Date])))
 
 
Thanks for your help!
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

Share some data and show the expected result in a simple table format.

  • Shelley's avatar
    Shelley
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Ashish_MathurI've tried three times now and powerbi community is not cooperating.

    Please see image I posted previously. Here is an example of contract cap data.

    BPIDCustomer Name (BPID)contr_numSAP Contract StartSAP Contract EndContract Cap
    100769396399ABC COMPANY (100769396399)80036564092/1/20181/31/2019$8,000
    100769396399ABC COMPANY (100769396399)80040815792/1/20191/31/2020$10,000
    100769396399ABC COMPANY (100769396399)80045755892/1/20201/31/2021$9,000
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      The visual that you want to create is immaterial.  First and foremost, we have to calculated the correct figures in a simple Table format.  So share your input Tables and show the exact expected result in a simple Table format.

      • Shelley's avatar
        Shelley
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        Ashish_MathurI'm trying to provide as you ask, and if this isn't it, I don't know what else to do. I'm trying to figure out how to sum the List price cumulatively for the list price transactions that occur between the start and end date range for each contract. In other words, the list prices accumulate from the start to the end of each contract. When the contract is over, the accumulation starts over again at 0. Any help in how to do this is greatly appreciated. Thank you.

         

        CONTRACT INPUTS    
        BPIDCustomer Name (BPID)contract_numSAP Contract StartSAP Contract EndContract Cap
        399ABC COMPANY (399)80036564092/1/20181/31/2019$     8,000.00
        399ABC COMPANY (399)80040815792/1/20191/31/2020$   10,000.00
        399ABC COMPANY (399)80045755892/1/20201/31/2021$     9,000.00

         

        DATA INPUTSDATA INPUTSDESIRED RESULT 
            
        Order Submit DateList_PriceCumulative UsageNotes
        2/1/2018 0Usage begins at 0 on 2/1/18, which is start of the contract above
        2/21/2018$2,592$2,592 
        8/23/2018$1,542$4,134 
        10/12/2018$1,709$5,843 
        10/17/2018$3,893$9,736 
        12/4/2018$513$10,249 
        2/1/2019 $0Usage begins at 0 again on 2/1/19, with a new contract
        2/25/2019$2,940$2,940 
        5/14/2019$1,186$4,126 
        7/18/2019$0$4,126 
        8/23/2019$323$4,449 
        9/2/2019$545$4,994 
        9/24/2019$2,636$7,630 
        2/1/2020 $0Usage begins again at 0 on 2/1/20
        2/7/2020$1,737$1,737 
        2/19/2020$128$1,865 
        2/20/2020$128$1,993 
            
  • Shelley's avatar
    Shelley
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Ashish_MathurHere's an example of the usage data: 

    Order Submit DateList_PriceCumulative UsageNotes
    2/21/2018$2,592$2,592Usage begins at 0 on 2/1/18
    8/23/2018$1,542$4,134 
    10/12/2018$1,709$5,843 
    10/17/2018$3,893$9,736 
    12/4/2018$513$10,249 
    2/25/2019$2,940$2,940Usage begins at 0 again on 2/1/19
    5/14/2019$1,186$4,126 
    7/18/2019$0$4,126 
    8/23/2019$323$4,449 
    9/2/2019$545$4,994 
    9/24/2019$2,636$7,630 
    2/7/2020$1,737$1,737Usage begins again at 0 on 2/1/20
    2/19/2020$128$1,865 
    2/20/2020$128$1,993 
  • Shelley's avatar
    Shelley
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Ashish_MathurPlease note I manually created the cumulative usage in Excel above, but this is what I'd like Power BI to do and then draw in a graph, which each new contract starting at 0. See how I have the entitlement amount working correctly, with the help of this post, but now how do I get the actuals to accumulate towards the cap? Thanks for your help!