> For the complete documentation index, see [llms.txt](https://kb.bevica.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://kb.bevica.com/documentation/reporting-and-analytics/bevica_power_bi/dates_and_time_intelligence_-bevica_power_bi.md). # 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](https://learn.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range) 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](https://learn.microsoft.com/en-us/dax/weekday-function-dax)) * 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](https://learn.microsoft.com/en-us/dax/weeknum-function-dax)) ### 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. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://kb.bevica.com/documentation/reporting-and-analytics/bevica_power_bi/dates_and_time_intelligence_-bevica_power_bi.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.