cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Missing DateHierarchy N DateIntelligence. Calculate diff. btwn. same period this yr vs last yr.

Hi All,

Pretty new user of Power BI here and faces some difficulties as per listed below.

We really hope that someone would gives advice/help on this.

As we are trying to make comparisons between sales performance of pervious year vs this year by Period, we definitely need “Date Intelligence” through “Quick Measures” in power Bi which could provide a great help on this calculation.

However, the date intelligence is missing which could be due no date hierarchy is available as well.

• What can be done to have the date intelligence and date hierarchy available or what are the other options? Are there any DAX expressions will be useful for this case if date intelligence and date hierarchy still couldn’t be enabled?

Example of raw data:

 Date Year Qtr Period Customer Sales 1/12/2018 2018 1 1 A 100 5/12/2019 2019 1 1 A 200 5/2/2018 2018 1 3 B 400 5/2/2018 2018 1 3 B 400 5/2/2019 2019 1 3 B 300

** Date column is not linked to any other tables

** Data type has already set to Date/Time

** Both date intelligence and date hierarchy are available automatically when we created a new table with a date column.

Note that we are trying to make a comparisons between pervious year vs this year from summarized data by Year, Period and Customer as per below desired output:

 Year Qtr Period Customer Sum of Sales Growth% 2018 1 1 A 100 2018 1 3 B 800 2019 1 1 A 200 100% 2019 1 3 B 300 -63%

Thank you.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Missing DateHierarchy N DateIntelligence. Calculate diff. btwn. same period this yr vs last yr.

Hi @JOO13

Create three measures

```total value =
CALCULATE (
SUM ( Table1[Sales ] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Year] = MAX ( Table1[Year] )
&& Table1[Period] = MAX ( Table1[Period] )
&& Table1[Customer ] = MAX ( Table1[Customer ] )
)
)

LY =
CALCULATE (
[total value],
FILTER (
ALLSELECTED ( Table1 ),
Table1[Year]
= MAX ( Table1[Year] ) - 1
&& Table1[Period] = MAX ( Table1[Period] )
&& Table1[Customer ] = MAX ( Table1[Customer ] )
)
)

growth = IF([LY]<>BLANK(),([total value]-[LY])/[LY])```

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

## Re: Missing DateHierarchy N DateIntelligence. Calculate diff. btwn. same period this yr vs last yr.

Thank you! This is definitely useful but we are interested to understand why both the Date Hierarchy and Date Intelligence are missing as well. I figured out that it is due to we used "Direct Query" instead of "Import" when we are retrieving the data from the database. Both Date Hierarchy and Date Intelligence are available only when the raw data are retrieved through "Import".

2 REPLIES 2
Community Support Team

## Re: Missing DateHierarchy N DateIntelligence. Calculate diff. btwn. same period this yr vs last yr.

Hi @JOO13

Create three measures

```total value =
CALCULATE (
SUM ( Table1[Sales ] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Year] = MAX ( Table1[Year] )
&& Table1[Period] = MAX ( Table1[Period] )
&& Table1[Customer ] = MAX ( Table1[Customer ] )
)
)

LY =
CALCULATE (
[total value],
FILTER (
ALLSELECTED ( Table1 ),
Table1[Year]
= MAX ( Table1[Year] ) - 1
&& Table1[Period] = MAX ( Table1[Period] )
&& Table1[Customer ] = MAX ( Table1[Customer ] )
)
)

growth = IF([LY]<>BLANK(),([total value]-[LY])/[LY])```

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

## Re: Missing DateHierarchy N DateIntelligence. Calculate diff. btwn. same period this yr vs last yr.

Thank you! This is definitely useful but we are interested to understand why both the Date Hierarchy and Date Intelligence are missing as well. I figured out that it is due to we used "Direct Query" instead of "Import" when we are retrieving the data from the database. Both Date Hierarchy and Date Intelligence are available only when the raw data are retrieved through "Import".

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors