Date table- Time Intelligence

Power BI
1

Hi,

I have data with only Year, Month and Quarter and not with actual dates. How do i utilize the time intelligence function to achieve best results.

I am new and getting to understand power BI, i am stuck here and looking for some help.

appreciate all what you can to help me progress in my learning.

krish

2

@krish1712,

Sam has a ton of great information on the site about date tables and time intelligence that you can find through the forum search function. Here are just a few that I found particularly helpful:

http://portal.enterprisedna.co/courses/103686/lectures/1772347

http://portal.enterprisedna.co/courses/303649/lectures/4673891

http://portal.enterprisedna.co/courses/103686/lectures/1772425

3

Hi Brian,

i did go through the information, it is useful.

But i still do not have an answer where in my fact table i do not have any date information for lookup (either Order date or ship date etc) but i have only year and month.
In those cases how does time Intelligence work?

regards
Krish

4

@krish1712

In those cases, you can create dates at the most granular level for each record in your fact table, and then use those dates to create relationships to your Date table. In your case, that would be month start date and month end date. Once you have those relationships established, you can use the standard time intelligence functions down to the month level (or make some assumptions to allocate your monthly data down to the day level).

Here’s an example with quarters as the highest level of granularity (PBIX posted at bottom), applying the DATEADD function to calculate YoY% change in sales.

Hope this is helpful.

  • Brian

image

Total Sales LY = 
    CALCULATE ( [Total Sales], DATEADD ( Dates[Date], -1, YEAR ) )

YoY % Change = 
VAR DeltaSales = [Total Sales] - [Total Sales LY]
RETURN
    IF (
        [Total Sales] <> BLANK ()
            && [Total Sales LY] <> BLANK (),
        DIVIDE ( DeltaSales, [Total Sales LY], 0 ),
        BLANK ()
    )

image

eDNA Forum - Time Intelligence with Year and Q.pbix (568.2 KB)

5

@krish1712,

Per the post above, if you’re interested in allocating your monthly data down to the daily level, I found a thread where Sam discusses changing granularity in detail and provides links to the relevant videos on the site:

6

@krish1712,

You may already have seen this, but Sam posted a fantastic video last week focused on dealing with financial data of different granularities. Directly relevant to your original question: