Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
digant
Helper I
Helper I

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

 

 

 

1 ACCEPTED SOLUTION

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

8 REPLIES 8
Phil_Seamark
Employee
Employee

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

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

 

single.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

 

 

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

@v-ljerr-msft

 

Thank you !! Worked perfectly.

 

regards

 

Digant

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.