Date Filter on top of USERELATIONSHIP

[digant]

Table Relationship

Volumedata for newly opened Account

TradeDate	VOLUME
31/10/2017	108048300.6
30/09/2017	87441377.16
Total	195,489,677.76

Hi,

I have defined table relationship and volume data for newly opened account as above. I am trying to get Volume of Newly Opened Account sliced by date filters.

Let's say I have chosen date filter(taken from calender as Sep 2017. I can think of below way to get volume but it will show value as 195 mil as my relationship is based on contractId. How can I filter that result set so as to filter records based on trade date between selected dates ?

Correct answer should be 87 mil.

NewlyTradeVolume =
CALCULATE (
    SUM ( rpt_pbi_sales_volume[VolumeAUD] ),
    USERELATIONSHIP ( rpt_pbi_sales_acs[sfContractID], rpt_pbi_sales_volume[sfContractID] )
)

Thank you in advance !!

Regards

Digant

[MattAllington]

Mmmm, this doesn't look like standard dimensional modelling. 

Consider removing the relationships from calendar to the 2 detail tables

set the relationships between header and details tables to active 

set 1 relationship only from calendar[date] to header[date]

maybe I'm. It understanding your data, but that is how I would set it up assuming the lower 3 tables are header and detail

[digant]

Hi @MattAllington

Thank you for your reply. acs is master and volume, withdrawal are detail tables. In case, if I remove that relationship, I will not be able to display Total Volume  / Total funding(not newly traded but for all) based on date filters. 

Moreover, removing that relationship is not bringing correct results.

Removed relationships

Regards

Digant

[MattAllington]

Well I don't know your data, but the original image doesn't look like good modelling to me.  Of course you will need to make it work for the data structure you have.  I don't know what acs master, volume and withdrawal details mean so I can't really help with that.

[digant · Accepted solution]

Hi,

I managed to get desired results by adding below measures.

Calendar table 

StartDate = CALCULATE(MIN('Calendar'[Date]), ALLSELECTED('Calendar'[Date])) 

EndDate = CALCULATE(MAX('Calendar'[Date]), ALLSELECTED('Calendar'[Date])) 

NewlyTradeVolume =
CALCULATE (
    SUM ( rpt_pbi_sales_volume[VolumeAUD] ),
    USERELATIONSHIP ( rpt_pbi_sales_acs[sfContractID], rpt_pbi_sales_volume[sfContractID] ),
    DATESBETWEEN ( rpt_pbi_sales_volume[TradeExecuted], [StartDate], [EndDate] )
)

Regards

Digant

[v-huizhn-msft]

Hi @digant,

Have you resolved your issue? If you have, welcome to mark the right reply as answer. More people will benefit from here. If you haven't, please feel free to ask.

Thanks,
Angelia