Previous month to date DAX syntax

[lochem]

Hi all,

I am trying to build a formula to compare MonthToDate (MTD) and Last month todate (LMTD) values. MTD is easy enough, however its the last month's calculation that i cannot get.
If today is 14-March, and my KPI ("myExpression") for the month to-date is currently at, say 250, I want to compare this value with the same measurement that is calculated for the same date range of February, from 1-Feb to 14-Feb.

However i am running into errors with my logic...

I am looking for something similar to below:

LastMTD:=CALCULATE([myExpression], DATESBETWEEN([Date_Column],start_date<start_date>,end_date<last_date>))

for start_Date <start_date>and end_date <end_date>i am using the following:

FirstDateLM:=FIRSTDATE(dateadd(datesmtd(OpsCalendar[Date]),-1,month))

and

LastDateLM:=LASTDATE(DATEADD(DATESMTD(OpsCalendar[Date]),-1,MONTH))

two undesirable things are happening:
1. in my calendar, the date column ends at 31 dec 2016. my firstdateLM and lastdateLM fields are returning Nov 1 and Nov 30 2016, instead of actual last month first day and actual 30 days ago, etc.

2. i am getting an error in my LastMTD formula as follows: "A Table of multiple values was supplied where a single value was expected"
im guessing this has something to do with first and last dates in the DATESBETWEEN function, but i cannot be certain.

Please help?</end_date></start_date></last_date></start_date>

 

[ImkeF]

Hi lochem,
try this:

LastMTD:=CALCULATE([myExpression], DATESMTD(DATEADD('Date'[Date_Column],-1,MONTH)))

 

[lochem]

Hi lochem,
try this:

LastMTD:=CALCULATE([myExpression], DATESMTD(DATEADD('Date'[Date_Column],-1,MONTH)))

Thanks for your go at this, but while this does work, it doesn't produce the right date range.
this calculates the expression for the entire previous month. i need the expression to be calculated on ONLY the same amount of days in the previous month as have already transpired in the current month.
e.g. if today is 18 march, i only want 18 days of feb to be evaluated.

 

[ImkeF]

But it should

Do you take a filter on a day-basis in your pivot-report?

Is your 'Date'[Date_Column] actually on a daily basis?

 

[lochem]

But it should

Do you take a filter on a day-basis in your pivot-report?

Is your 'Date'[Date_Column] actually on a daily basis?

I am actually trying to use this more in PowerView than in pivot tables.
actually, when i try to use this without any filters applied in pivottables and powerview, the result is blank.

this formula only returns a value when a date filter is applied separately; for example, if i select March 2015 in a slicer, then it will return the result for Feb 2015, but again, the whole month.
however, if i put this in a pivot table by itself, with no rows or columns, it's just blank.

and yes, my 'Date'[DateColumn] is continuous from Jan 1 2014 to Dec 31 2016.

 

[lochem]

There could be something in this blog post MDX and DAX topics: DAX Time Intelligence Functions

The author states:

"A special rule regarding datetime filter inside Calculate/CalculateTable
If a Calculate filter has a unique column that is of data type date/time, all previous filters on all columns from the table which contains this date/time column are removed. This hacky feature implies that

Calculate(expression, TI function) = Calculate(expression, TI function, All(DateTable)).

...Let’s say you have some years on the pivot-table row and then you drag a measure which uses time-intelligence function DateAdd to show sales from the previous year. The author of the measure formula may not realize that DateAdd function only returns a single column of Datekey which overwrites existing filter on the same column. This special rule makes sure that the filter on the CalendarYear column, which comes from the pivot-table, is also removed so you get back the expected result. Without this special rule, Calculate(expression, TI function) would set days of the previous year on the Datekey column but leave the previous year as the filter on the CalendarYear column. The conflicting filters would have produced a blank result."

So now i'm wondering if this could be my issue... but the only solution he proposes is to have a dedicated date table,date column with a relationship to the fact table, and i have all that...

 

[lochem]

Problem solved.

The issue was that i was pointing to the date_column of the main calendar table; when i pointed instead to the local column of dates in the fact table, everything works!

 

[scottsen]

Beware that this solution could blow up on you. If say, there was a date with no facts in your fact table... the time intelligence functions would error out on you (they require a continuous range of dates). Operating against the calendar table is technically "correct", and Imke's solution sounded correct to me... though, I dont do a ton of Power View.

I do get the feeling you are not quite understanding what you are asking

If you select, Mar 2015... of course you get all of Feb for prior month to date. If you want just to PART of the month, you need to specific what part. That is why Imke is asking if you put an individual date on rows... cuz that should do what you want. eg: for mar 15, you will see all facts for Feb 1-15. It's take the LAST day of your current month (mar 31) if you choose the whole month, ... so then you would get Feb 1-Feb28/29.

 

[lochem]

Beware that this solution could blow up on you. If say, there was a date with no facts in your fact table... the time intelligence functions would error out on you (they require a continuous range of dates). Operating against the calendar table is technically "correct", and Imke's solution sounded correct to me... though, I dont do a ton of Power View.

I do get the feeling you are not quite understanding what you are asking

If you select, Mar 2015... of course you get all of Feb for prior month to date. If you want just to PART of the month, you need to specific what part. That is why Imke is asking if you put an individual date on rows... cuz that should do what you want. eg: for mar 15, you will see all facts for Feb 1-15. It's take the LAST day of your current month (mar 31) if you choose the whole month, ... so then you would get Feb 1-Feb28/29.

Thanks for the warning. there are no records in the Fact table that have blank cells in the fact column. I check this regularly, so i should be ok. still, its good to keep in mind, so thanks.

 

[scottsen]

Clarifying, the concern would be "the fact table had nothing for Feb 12". Or *any* other day. time intelligence functions in dax must have a row for EVERY day... which is why there are typically built off a separate calendar table.