cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CRamirez Regular Visitor
Regular Visitor

Sales table not recognizing date table hierarchies

Background:

I am currently running into an issue where my sales fact table is not recognizing my date table hierarchies beyond the date. I am attempting to create a visual that displays sales quantities for the past five quarters as well as the growth % of those quarters over the previous year’s respective quarter. For Example:

 qtr-example.PNG

 

This is exactly the visual I would like to display in my current report. The thing is, I created this visual in PowerBI already for a previous report when I first started learning the program. The report was built utilizing imported non-normalized data from a Microsoft Analysis Services cube and was very unwieldy.  For some reason I cannot recreate this visual, specifically the growth %, in my current dataset.

 

This visual was created through the combination of these three DAX measures:

Sales QTY Sold =
SUM ( [SalesQuantity_Sold] )

LY QTY Sold =
CALCULATE ( [Sales QTY Sold], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

LY QTY Sold Growth% =
IFERROR ( ( [Sales QTY Sold] - [LY QTY Sold] ) / [LY QTY Sold], BLANK () )

These measures work beautifully and give me accurate data in the exact way I’m looking for. The referenced ‘Calendar’[Date] data is built using the Calendar function which is what my time intelligence functions are based from. The measures are then used in a Line and clustered column chart like this:

chart-setup.PNGYearQTR is a formatted column from my date table which is written like this:

 

YearQuarter =
FORMAT ( [DateKey], "YYYY" ) & "Q"
    & FORMAT ( [DateKey], "Q" )

 

Then why am I asking for help?

I needed to create a visual very similar to this in a new report I’m working on. I wanted to use our SQL Server database that utilizes normalized tables which makes building datasets much easier. I originally tried this through DirectQuery, but quickly found out that date hierarchies aren’t recognized in DirectQuery. So, I Imported the SQL tables which is the main difference between these two reports because I used a non-normalized Microsoft Analysis Services cube for the first one. After setting everything up exactly the same way as before, my date table, DAX measures, and visualization and this was the result:

 

qtr-problem.PNG

 

 After some testing, I’ve realized that my Sales fact table is not recognizing date hierarchies past the date. If I try to roll up to monthly/qtrly/yearly I end up with blank values for last year’s totals. A good example is that if I put this data into my previous report, the table would display like this:

 

table-date-sales.PNG

 

If I tried this same table in my current report, I end up with a blank table:

 

table-date-issue.PNG

The only way to get previous year data to actually appear in my current report is to display it against the date column without the hierarchy like this:

 

table-date-nohierarchy.PNG

 

I’m at my wits end trying to figure this out. Is it simply due to normalized vs. non-normalized table structures? Thank you for taking your time to read, any help would be greatly appreciated.

3 REPLIES 3
Super User
Super User

Re: Sales table not recognizing date table hierarchies

Hi,

 

The YearQTR column should be created in the calendar table and dragged from there to your visual.

CRamirez Regular Visitor
Regular Visitor

Re: Sales table not recognizing date table hierarchies

Hello @Ashish_Mathur!

 

Sorry if I wasn't entirely clear, but the YearQTR column is on the calendar table. It is also dragged from there onto the visual.

Thank you,

 

Super User
Super User

Re: Sales table not recognizing date table hierarchies

Hi,

 

Share the link from where i can download the file.  Please also show the problem clearly there.