Measure Sales YTD previous year excluding current month =įILTER('Calendar' 'Calendar'calendar table should be marked as a date table. When using DAX Time Intelligence functions, it is always recommended or required to have a continuous calendar without a break in the dates. This function is useful for clearing filters and creating calculations on all the rows in a table. Measure last selected month sales sum with time intelligence =Ībove ALL function returns all the rows in the calendar table, ignoring any filters that might have been applied. However, in more complex scenarios, the approach above with variables can be useful. In this case, the time intelligence function below will deliver the same result. RETURN CALCULATE(SUM(Sales) end_date_filter) VAR end_date_filter = FILTER(ALL('Calendar') Calendar= end_date_value) However, there is a workaround with variables if you want to use date value as a filter: Measure last selected month sales sum with variables = MAX(date) will not work as a filter in the CALCULATE function as it returns a value, not a table. So prefer MIN(date) and MAX(date) returning values, not LASTDATE(date) or FIRSTDATE(date) if you do not use dates as a filter or table filter expression!Īs default e.g. Therefore, Time Intelligence functions do not provide the best calculation performance to figure out a static date value. CALCULATE command can use these table filter expressions. Time Intelligence functions typically return tables or table filter expressions. Theory â Data selection with time intelligence As an example, below screenshot yellow circled data, compare 2002 sales at the left to 2001 sales at the right). Calendar dates are displayed for the selected calendar period, but the data is from the period defined by the time intelligence function. Therefore, it really makes sense to do a lot of date operations to calendar table with Power Query and use DAX in only special individual cases.Ä«) Data selection with time intelligence (DAX Time Intelligence related functions). Furthermore, with Power Query, the load of the data happens when the report updates. Typically, same date patterns repeat in multiple measures. See my post Power BI Financial Date Table. You cannot filter out the months behind the values you are displaying or you will not see the data.Ĭonsider that all of the basic date selection can be done in Power BI Power Query to the calendar table instead of using DAX. Result is displayed for the selected dates. From DAX point of view, I make the following simplified division: A) Filtering data with dates and B) Data selection with time intelligence.Ī) Filtering data with dates (DAX date functions). The business goal is typically to calculate and filter based on dates. In this post is reviewed DAX date and time intelligence function theory and usage examples. However, by pareto principle a couple of them will solve most use cases. Power BI DAX has a long list of different Date and Time Intelligence related functions.
0 Comments
Leave a Reply. |