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

     

     

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
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