cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JOO13 Regular Visitor
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
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])

1.png

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.

View solution in original post

JOO13 Regular Visitor
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". 

View solution in original post

2 REPLIES 2
Community Support Team
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])

1.png

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.

View solution in original post

JOO13 Regular Visitor
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". 

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 336 members 3,414 guests
Please welcome our newest community members: