Sales table not recognizing date table hierarchies

[CRamirez]

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:

 

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:

YearQTR 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:

 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:

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

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:

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.

[Ashish_Mathur]

Hi,

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

[CRamirez]

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,

[Ashish_Mathur]

Hi,

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