Forum Discussion
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.
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
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.
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
TradeID TradeVolume TradeExecuted 1 5000 1/01/2016 2 10000 2/01/2016 3 7000 20/01/2016 Performance Table
TradeID Pnl PnlDate 1 100 1/01/2016 1 200 2/01/2016 1 300 15/01/2016 2 3000 2/01/2016 POWER BI Table looks (for date btween 1st JAN and 20th JAN)
Current
TradeExecuted TradeID TradeVolume Pnl 1/01/2016 0:00 1 5000 100 2/01/2016 0:00 2 10000 3200 20/01/2016 0:00 3 7000 I would like to see below data.
TradeExecuted TradeID TradeVolume Pnl 1/01/2016 0:00 1 5000 600 2/01/2016 0:00 2 10000 3000 20/01/2016 0:00 3 7000 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]))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
TradeID TradeVolume TradeExecuted 1 5000 1/01/2016 2 10000 2/01/2016 3 7000 20/01/2016 4 12000 1/01/2016 Performance
TradeID Pnl PnlDate 1 100 1/01/2016 1 200 2/01/2016 1 300 15/01/2016 2 3000 2/01/2016 4 300 2/01/2016 4 400 3/01/2016 4 500 7/01/2016 4 900 1/01/2016 POWER BI(Date selected 1st JAN)
TradeExecuted TradeID TradeVolume Pnl 1/01/2016 0:00 1 5000 600(Correct 100) 1/01/2016 0:00 4 12000 2100(Correct 0) Thank you !!
regards,
Digant
