Forum Discussion
PREVIOUSMONTH
- 10 years ago
You have to read the fine print here to understand how PREVIOUSMONTH (and similar functions) work:
"This function returns all dates from the previous month, using the first date in the column used as input. For example, if the first date in the dates argument refers to June 10, 2009, this function returns all dates for the month of May, 2009."
So, mainly you have to use this in a "context aware way". Here is the example from the page:
Example
The following sample formula creates a calculated field that calculates the 'previous month sales' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a calculated field, named Previous Month Sales, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey]))
My understanding of how this works is that the row labels are context filters that filter the 'DateTime' table to that specific year and month, so in the context of the pivot table (matrix) for each particular row, the 'DateTime' table has been filtered such that when you pass PREVIOUSMONTH the 'DateTime'[DateKey] column, you are only passing in the dates for a specific year and month, like June 2009. Thus, PREVIOUSMONTH sees that and goes and grabs the dates for the previous month and passes those back as filters to the CALCULATE function. I don't think that the page really explains it all very well, but that is my understanding of how it is supposed to work.
Here is a good article on context in DAX formulas that might help as well.
PBI filters do not allow programmatic criteria. You can't set the filter on a date to < TODAY().
You have to set a literal value in a filter.
This makes it seems like an awful problem to solve, but it's actually trivial to do at a level before the presentation layer.
Create a field in your date dimension for CurrentYTD. Then you can filter on [CurrentYTD] = True.
Power Query offers a very useful function for adding this field to any date dimension Date.IsInYearToDate(). As you refresh your model daily, this field will always be up to date, and you never have to change your filter criteria.
Hi Greggyb
You wrote:
Create a field in your date dimension for CurrentYTD. Then you can filter on [CurrentYTD] = True.
How can I do this?
I'm currently struggeling with time issues, this might help me. I already added and linked a date table:
Thanks and best regards
Patrick