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

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