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 Performanc...
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

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]))