Dates and Time Intelligence (Bevica Power BI)

How date tables and time intelligence filters work in Bevica Power BI.

Dates and Time Intelligence (Bevica Power BI)

TABLE OF CONTENTS

• Date Table Conventions
• Time Intelligence Functions
• Date Filters for limiting periods

  • Ignore dates in the future which don't have entries
  • Up to the same date last year
• Examples

The Time Intelligence filters allow you to filter to specific time periods with the main purpose of being able to easily compare time periods.

An important concept is that for most of these to work they need to be date bound. That is, somewhere within the context of your report there needs to be a filter which defines a date value. Conceptually think of it like this - if I ask you what Sales were this year you need to know what the current year is! So when using this on reports you need to tell the report, and therefore the underlying measures, what the year is too.

Relative Date Filters can be used (and are on a number of the default reports) to apply a filter. Refer to standard Power BI documentation: Create a Relative Data Slicer & Filter

Note the Financial Year calculations are dependant on setup.

Date Table Conventions

The date table uses full years based on dates in the date model, checking the min and max date of ledgers, budgets etc. to generate a full date table.

The date table uses the following conventions:

• Weeks begin on Sunday (Sunday = Week Day 1) (Weekday)
• Week Numbers uses a calendar convention in which the week containing January 1 is considered to be the first week of the year (Week Numbers)

Time Intelligence Functions

Name	Definition	Comments
Current	The currently selected period(s)	Allows for selection across multiple periods
MTD	Month to Date	Current month, or max of the current month if filtering by say year
MTD LY	Month to Date Last Year	As above, but for 1 year ago.
MoM LY	Month over Month Last Year	Month this year vs the same month last year
MoM LY %	Month over Month Percent	Above as a % vs LY
YTD	Year to Date (Calendar)	January to the max current date according to your filters.
YTD LY	Year to Date Last Year (Calendar)	Same period last year
LY	Last Year	Based on the currently selected date range
YoY	Year over Year	Current less LY
YoY %	Year over Year Percent	YoY divided by LY
This Week	This week	Based on the Week Number, so could be a part week
Prior Week	Previous Week	Based on the Week Number
Vs Prior Week	This week less Prior Week
Last Month	Previous calendar month	If using December 2021 this would be November 2021
Vs Last Month	This month less last month
Cumulative Total	Running total from the start of all transactions to the max of your current date filter	The main use is for inventory valuations where you need to see the sum of all transactions both +/- to arrive at a current value.
Cumulative Total LY	As above but only up to 1 year ago
Cumulative Total YoY	This year less last year
Cumulative Total YoY %	Percent variance
FYTD	Financial Year to Date
FYTD LY	Comparison value from last financial year
FYoY	This FY vs. last FY
FYoY%	As a percentage
MAT (365)	Moving Annual Total for 365 days	Sept 2023
MAT LY (365)	Moving Annual Total for prior year	Sept 2023
MAT vs LY	Difference for above	Sept 2023
MAT vs LY%	Difference in % for above	Sept 2023

Date Filters for limiting periods

This only applies to models after Sept 2023. These values update when the data model is refreshed.

Ignore dates in the future which don't have entries

You can use the Date has ILE Posting (on the Core Dataset) to filter out dates in the future. This is useful sometimes when using Cumulative Totals.

Up to the same date last year

As standard measures like YTD LY will include the full date range of the previous year.

For example if your current date is for 01/09/2023 then

• YTD will be sales 01/01/2023 to 01/09/2023
• YTD LY would be 01/01/2022 to 31/12/2022

To enable calculations like 'Year to Date last year up to the equivalent date' then you can apply the filter from the Dates table Same Date LY has ILE Posting. By applying this filter the above would be:

• YTD will be 01/01/2023 to 01/09/2023
• YTD LY will be 01/01/2022 to 01/09/2022 (dates above 01/09/2022 are filtered out with the above)

Fixed Time Intelligence

This only applies to models after June 2024.

There are a number of "Fixed Time Intelligence" measures. These are designed to allow users to add individual measures which are separate from using the Time Intelligence filter table.

As an example you could have:

• Quantity, Sales, Margin, Margin % for the current period (e.g. Sales, Invoiced (ILE) Period)
• Only the Sales variance for the same period LY (e.g. Sales, Invoiced (ILE) Period vs. Period LY)

Examples

Dates are being filtered (1) by 2021-01, 2021-02 and 2021-03. The date ranges then are 01/01/2021 to 31/03/2021.

• Current is Sales within this date range. This is effectively the same as YTD and the comparative values YTD LY, LY, YoY, YoY % are working across the same date range and summing sales in 01/01/2020 to 31/03/2020.
• MTD is the highest month - so March 2021 in this instance.

Looking at some of the other values then:

• Current is still 01/01/2021 to 31/03/2021 and MTD is March 2021 only.
• Last Month is Feb 2021 and the Vs Last Month is the difference from March 2021 to March 2020.
• Cumulative Total is cumulative sales from the start of time (in the context of your report!) to 31/03/2021.
• Cumulative Total LY stops at 31/03/2020.

If we change the date filter to be 2021-02 and 2021-03 then the date range is 01/02/2021 to 31/03/2021.

• Current is now only within this period, so is different to YTD (sales in January aren't in 'Current').
• YTD LY is looking at 01/01/2020 to 31/03/2020 still.
• The LY, YoY and YoY % are now comparing Feb and March 2021 to Feb and March 2020.

Using these filters you can build analysis which might look at say sales over time to easily be able to compare values.