Forum Discussion
Time intelligence question- pulling prior month data
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
2 Replies
- 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?
