Forum Discussion

digant's avatar
digant
Helper I
9 years ago
Solved

Sum data based on date range

Hi,

 

Can someone please help me, how I should define below solution in Power BI. 

 

Data

1) I have Trade table, which has trade related information for all executed dates.

2) I have Performance table which has PnL information per day. 

 

Requirements

 

1) User can select any date range using date range control.

2) Let's say user selected date range between 1st JAN and 12th FEB.

 

In this case,

 

I want to SUM UP all trades between particular dates

I want to SUM UP all performance between particular dates

Once I have those SUM, I would like to join table based on three keys to display my final data in report.

 

I am not joing table before doing SUM because, there could be case wherein Trade tables doesn't have Trade information on particular day but Performance table may have information for that particular day(considering trade was open and PnL generated for historical trade).

 

Can you please guide me in terms of implementing above solution?

 

regards,

 

Digant

 

 

 

Need Help
Show and Tell
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi digant,

    I tried changing both Crosss filter direction Performance - Trade & Trade - Calendar relationships to Single, but still can not define relationship between( not even single) Perfmance  - Date table. 

    In this scenario, you can create an inactive relationship between Performance  - Date table like below.

     

     

    Then you should be able to create a measure to calculate the sum of Performance[Pnl] using USERELATIONSHIP Function (DAX), and show the measure on the report with other columns.

    PnlVolume =
CALCULATE (
    SUM ( Performance[Pnl] ),
    USERELATIONSHIP ( 'Date'[Date], Performance[PnlDate] )
)

    Here the sample pbix file for your reference.:smileyhappy:

     

    Regards

8 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi digant,

     

    I recommend you create a Date/Calendar table.  You can do this in DAX using the CALENDARAUTO() function  

     

    eg.

     

    Dates = CalendarAuto()

    Create a relationship between your Trade table and the Dates table

     

    Then create a relationship between your Performance Table and the Dates table.

     

    Use the Date field from your Dates table in a Slicer.  This will give you the option to set date ranges.

     

    Once you have that, simply create SUM measures on each of your Trade and Performance tables and if you drag these to a visual, along with Date from your Dates table, I think you will be close to what you need.

     

    How does that sound?

     

    The key is you don't need a relationship between Trade and Performance from the info you have given.

    • digant's avatar
      digant
      Helper I
      9 years ago

      Hi Phil,

       

      Thank you for your reply !! 

      Although I have created table with dates and defined relationships, SUM is not calculated properly. 

       

      I wanted SUM in Performance, which will be utilized Trade table to be grouped by TradeID between two dates that I am passing but it always groups on Date rather than TradeID.

       

      Moreover, I can not define any relationship between Trades and Performance table on TradeID.

       

       

      TRADE Table

      TradeIDTradeVolumeTradeExecuted
      150001/01/2016
      2100002/01/2016
      3700020/01/2016

       

      Performance Table

      TradeIDPnlPnlDate
      11001/01/2016
      12002/01/2016
      130015/01/2016
      230002/01/2016

       

      POWER BI Table looks (for date btween 1st JAN and 20th JAN)

      Current 

      TradeExecutedTradeIDTradeVolumePnl
      1/01/2016 0:0015000100
      2/01/2016 0:002100003200
      20/01/2016 0:0037000 

       

      I would like to see below data.

      TradeExecutedTradeIDTradeVolumePnl
      1/01/2016 0:0015000600
      2/01/2016 0:002100003000
      20/01/2016 0:0037000 

       

      Thank you in advance !! 

       

      regards,

       

      Digant

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi digant

         

        I have a working solution but only IF the TradeID column is unique in your TRADE table.

         

        If it is, create a relationship between Trade and Performance on TradeID (Trade = oneside and Performance = Many side)

         

        Then just add the following calculated column to the Trade Table

         

        New Column = CALCULATE(SUM('Performance'[Pnl]))