Time intelligence question- pulling prior month data

[Anonymous]

Hi All,

I have a problem with the time-intelligence based measure.

Its purpose is to pull the value of prior month's exchange rate from a table, which looks as follows:

Table name is 'UnpivotedBasic':

The measure to pull the prior month's rate is (not working):

Exchange rate from prior month=CALCULATE(SUM(UnpivotedBasic[Value]),DATEADD('Calendar'[Date],-1,MONTH))

Functions work separately, I mean if I enter: CALCULATE(SUM(UnpivotedBasic[Value])) it shows the value for the current month and if I type in only DATEADD('Calendar'[Date],-1,MONTH) it correctly shows the prior month but if combined the 'blank' is returned....

'Calendar'[Date] is a date table and date range is continuous therein. Accounting rates are only published monthly hence I created a separate 'Calendar'[Date] table. 

Regards

Krzysztof

[AllisonKennedy]

@Anonymous
I'm not sure exactly what your question is - what do you mean it shows the month when you only use the DATEADD?

From what little detail I know about your data, I suggest maybe using a MAX instead of a SUM just in case you ever get more than one entry per month.

PrevMonthExchangeRate=CALCULATE(MAX(UnpivotedBasic[Value]),DATEADD('Calendar'[Date],-1,MONTH))

What visual are you trying to use the measure in? Can you confirm it's a MEASURE and not a column?

[Anonymous · Accepted solution]

Thank you. I've just solved the issue. It looks the reason was that some other slicer was in conflict with this measure. Having added the 'ALL' function within Calculate helped and it's now working ! 🙂