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

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

 

digant's avatar
digant
Helper I
9 years ago

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

 

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

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

     

    • digant's avatar
      digant
      Helper I
      9 years ago

      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

       

       

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        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