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

     

     

     

thugthug's avatar
thugthug
Frequent Visitor
8 years ago

i tried to make it a cumulative line, but unfortunately i messed it up.

Running Actuals = CALCULATE(SUM(Table1[Contract amount],FILTER(ALL(Table1[Contract start date],'Calendar'[Date].[Date]<=MAX('Calendar'[Date].[Date])))))

Is there any measure for this, but cumulative?

Shelley's avatar
Shelley
Post 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
    Super User
    6 years ago

    Hi,

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

    • Shelley's avatar
      Shelley
      Post 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
        Super 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
      Post 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
      Post 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!