Previousmonth AND Dateadd

[jon999]

Hi

I am trying to working out the last month and used Calculate([measure],previousmonth(calendar[date]) and I get the list from July to December but don't get any numbers from January to June. I am using a financial year end date 30 June. When I use Dateadd I get an error message about only works with contiguous date selection when I do have a list of dates.

How do I get it so it will pick up the financial year (1 July - 30 June) and not a calendar year.

Thanks

Jon

 

[Matt Allington]

Previousmonth takes the first date in the current filter context, then returns the previous entire month. It makes no difference if it is a fin year or not - previous month is previous month.

What do you mean you "get the list"?

I would use calculate([measure],dateadd(cal[date],-1,month))

 

[jon999]

Hi Matt

As always thanks for the reply.

What I meant by get the list was I have a calendar table which has each individual dates for 2 years.

When I use the dateadd formula you mentioned I can an error message:

MdxScript(Model) (6,62) Calculation error in measure 'Data[Measure 1]: Function 'DATEADD' only works with contiguous date selections.

Thanks

Jon

 

[Matt Allington]

Yes, many time intelligence functions need contiguous date ranges. If you put months on rows in a pivot, but don't put a filter on year, then Jan will return Jan for multiple years. So you must have a filter on year as well as something below that - no greater than month in your case.

Put year on rows, then put month on rows too, then the measure on values

 

[jon999]

Hi Matt

I got it to work. Thanks for your help.

Jon