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_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

      • digant's avatar
        digant
        Helper I
        9 years ago

        v-ljerr-msft

         

        Thank you !! Worked perfectly.

         

        regards

         

        Digant