Reply
Regular Visitor
Posts: 15
Registered: ‎03-14-2017
Accepted Solution

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

 

 

 


Accepted Solutions
Super Contributor
Posts: 1,821
Registered: ‎07-17-2016

Re: Sum data based on date range

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.

 

relationship.PNG

 

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

result.PNG

Here the sample pbix file for your reference.Smiley Happy

 

Regards

View solution in original post


All Replies
Super Contributor
Posts: 1,354
Registered: ‎11-29-2015

Re: Sum data based on date range

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.

Regular Visitor
Posts: 15
Registered: ‎03-14-2017

Re: Sum data based on date range

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

Super Contributor
Posts: 1,354
Registered: ‎11-29-2015

Re: Sum data based on date range

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

Trade.png

 

Regular Visitor
Posts: 15
Registered: ‎03-14-2017

Re: Sum data based on date range

Hi @Phil_Seamark 

 

Thank you for your reply, but I can not add all three relationship together.

 

1) Many to one relationship between Trade and Calendar table

2) Many to one relationship between Performance and Calendar table

3) Many to one relationship between Performance to Trade table on TRADEID

 

If I deactivate Performance and Calendar relation to add third relation then Performance data doesn't respect date filters.

 

 

TRADE

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

 

Performance 

TradeIDPnlPnlDate
11001/01/2016
12002/01/2016
130015/01/2016
230002/01/2016
43002/01/2016
44003/01/2016
45007/01/2016
49001/01/2016

 

POWER BI(Date selected 1st JAN)

TradeExecutedTradeIDTradeVolumePnl
1/01/2016 0:0015000600(Correct 100)
1/01/2016 0:004120002100(Correct 0)

 

Thank you !!

 

 regards,

 

Digant

 

Super Contributor
Posts: 1,354
Registered: ‎11-29-2015

Re: Sum data based on date range

Are any of the relationships BI -Directional?  Make sure they are set to Single 

 

single.png

Highlighted
Regular Visitor
Posts: 15
Registered: ‎03-14-2017

Re: Sum data based on date range

Hi @Phil_Seamark,

 

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.

 

 

regards,

 

Digant

 

 

 

Super Contributor
Posts: 1,821
Registered: ‎07-17-2016

Re: Sum data based on date range

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.

 

relationship.PNG

 

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

result.PNG

Here the sample pbix file for your reference.Smiley Happy

 

Regards

Regular Visitor
Posts: 15
Registered: ‎03-14-2017

Re: Sum data based on date range

@v-ljerr-msft

 

Thank you !! Worked perfectly.

 

regards

 

Digant