DAX measure to get rate from dislocated table

[RicoS]

Hi,

I am struggling to return a result with a Dax formula. I have a Fact table that has some data

Fact Table[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Account[/TD]
[TD]Curr[/TD]
[TD]Trans Amt[/TD]
[TD]GBP Equiv[/TD]
[TD]USD Equiv[/TD]
[/TR]
[TR]
[TD]01/04/18[/TD]
[TD]12345[/TD]
[TD]EUR[/TD]
[TD]10,000[/TD]
[TD]9,000[/TD]
[TD]14,000[/TD]
[/TR]
[TR]
[TD]01/05/18[/TD]
[TD]12445[/TD]
[TD]USD[/TD]
[TD]15,000[/TD]
[TD]10,000[/TD]
[TD]15,000[/TD]
[/TR]
[TR]
[TD]01/10/18[/TD]
[TD]12445[/TD]
[TD]GBP[/TD]
[TD]1,000[/TD]
[TD]1,000[/TD]
[TD]1,425[/TD]
[/TR]
</tbody>[/TABLE]

I have a dislocated (disCurr) table (no relationship, I just use it for a slicer) that allows the user to select GBP or USD as the report currency and a measure called report amount which returns either GBP or USD equivalent based on that selection. This works without issue. This measure returns a cumulative values based on a date table, again without issue:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ReportAmount:=IF(MAX(disCurr[Report Currencies])="GBP",CALCULATE(SUM([GBP Equiv]),FILTER(ALL(dDates),dDates[Date]<=MAX(dDates[Date]))),IF(MAX(disCurr[Report Currencies])="USD",CALCULATE(SUM([USD Equiv]),FILTER(ALL(dDates),dDates[Date]<=MAX(dDates[Date]))),0))[/FONT]

However, my transaction table's USD Equivalent is based on the date entered into the table, so I have a rates table that I want to use to get the USD/GBP equiv at the maximum date selected in the date slicer, which I can then use to multiply by SUM([Trans Amt]) and get the value of the transaction at today's rate.

I have a dRates table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Curr From[/TD]
[TD]Curr To[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]01/05/18[/TD]
[TD]EUR[/TD]
[TD]GBP[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]01/05/18[/TD]
[TD]EUR[/TD]
[TD]USD[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD]01/05/18[/TD]
[TD]GBP[/TD]
[TD]USD[/TD]
[TD]1.4[/TD]
[/TR]
[TR]
[TD]01/10/18[/TD]
[TD]EUR[/TD]
[TD]GBP[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]01/10/18[/TD]
[TD]EUR[/TD]
[TD]USD[/TD]
[TD]1.3[/TD]
[/TR]
[TR]
[TD]01/10/18[/TD]
[TD]USD[/TD]
[TD]GBP[/TD]
[TD]1.54[/TD]
[/TR]
</tbody>[/TABLE]

I have a unique currencies (uCurr) table with a relationship from fact table and to the rates table

fTable[Curr]->uCurr[Curr]
uCurr[Curr]->dRates[Curr From]

In my pivot table I output [Account], uCurr[Curr], [Report amount] and I was hoping to retrieve the Rate with a simple calculate measure:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]DateRate:=IF([ReportAmount]=0,BLANK(),CALCULATE(SUM(dRates[Rate]),FILTER(dRates,dRates[Curr From]=MAX(uCurr[Curr])&&dRates[Curr To]=MAX(disCurr[Curr])&&dRates[Date]=MAX(dDate[Date])))[/FONT]

However this returns nothing (it does not return an error, just blank and the report amounts are visibly not blank in the row)

I split the Measure into 3 separate measures and populated my pivot table with them to check to see if they returned the correct answers:

LineCurr:=IF([ReportAmount]=0,BLANK(),IF(HASONEFILTER(uCurr[Curr]),MAX(uCurr[Curr]),"N"))

RateDate:=[LEFT][COLOR=#222222][FONT=Verdana]IF([ReportAmount]=0,BLANK(),MAX(dDate[Date]))

ReportCurr:=[LEFT][COLOR=#222222][FONT=Verdana]IF([ReportAmount]=0,BLANK(),MAX(disCurr[Curr]))

All the above return the expected values (for example, LineCurr as per each line in the fact table, RateDate as per the date slicer and ReportCurr as per the Report currency selected in its slicer)

When I combine into one measure, I just cannot seem to get the Rate returned. I even substituted my above measure into my original formula, without success again:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

DateRate:=IF([ReportAmount]=0,BLANK(),CALCULATE(SUM(dRates[Rate]),FILTER(dRates,dRates[Curr From]=[LineCurr]&&dRates[Curr To]=[ReportCurr]&&dRates[Date]=[RateDate])))

At this point, I'm a little stuck! Any help would be much appreciated. DAX sometimes has that ability to make something that seems very simply be very hard (but with great rewards when correct)!

​

[/FONT]Cheers,
Rico
_{^{<strike>
</strike>}}[/FONT][/COLOR][/LEFT]_{^{<strike>
</strike>}}[/FONT][/COLOR][/LEFT]