Issue with data gaps, matrix view / hierarchy with time functions

[JC3]

Struggling a bit with Matrix view and hierarchy relationships, combined with time function. 

I have a fact table with sales; each row contains information with month, region, country, and product portfolio. I'm comparing YTD sales with last YTD sales in same period. Function works, but is breaking as I drive down though the hierarchy Region>>Country>>Portfolio 

What I'm finding is that the issue's driven by gaps in portfolio... For example, as I drive down EMEA>>Spain>>Porfolio, "Product D" has zero sales in one of the months used in calculation... and this breaks the matrix visualization once I drive to Porfolio level. I can fix the issue by appending on a table with rows of zero revenue sales, or by filtering out the product in question... neither are good alternatievs... I'd like to drive from global down to portfolio and have a complete view. 

Note that most of the topics I've seen on this involve discussion around a complete Date Table in addition to Fact table, and linking them up. I've done this... but frankly it doesn't seem to help, and connecting/breaking this Relationship appears to have no impact on calculation, and if I link direct to Date Table in calculation the formulas aren't working.  

Total Sales = sum('Sales 2015 to JUN 2017'[Sales_USD])

Total Sales LY = CALCULATE([Total Sales],dateadd('Sales 2015 to JUN 2017'[CAL_DATE],-1,YEAR))

Any help appreciated

[JC3]

Back to this. I have a date table that's connected, so I'm contiguous across dates... that's not causing the break (I've tested around this and everything's working.) 

The issue is gaps in products. For example, in Portfolio some Countries have zero sales of a given item. in that Portfolio This isn't expressed as a zero in the data table; there's just no entry at all. In these cases, the visualization break on the calculate of the "Last Year" value: 

Total Sales = sum('Sales 2015 to JUN 2017'[Sales_USD])

Total Sales LY = CALCULATE([Total Sales],dateadd('Sales 2015 to JUN 2017'[CAL_DATE],-1,YEAR))

One solution is to add "Zeros" to the data table to cover all potential data gaps across every combination of Country, Date, Portfolio, or include Zero sales items... but my data guy and I both think there must be a more elegant solution. 

[CRBam]

Hi, did you ever come across a better solution? I may be having a siimlar issue...

[CahabaData]

am wondering about the comment "breaks the matrix visualization " - - is it a real technical glitch or a matter of not generating an image you prefer?

I ask in that over the ages, in reporting for database apps - - there has always been a hassle in dealing with nulls in terms of math and display.... having to stuff 0s and such....   so am wondering if this is an issue of managing the display or if there is a real functional error....

[JC3]

It's not a formating issue... I'm getting the "non-contiguous data" error with X through box... the data's breaking the visualization. 

I actually tried removing blanks and replacing with zeros just to see if it made a difference... it didn't.

[CahabaData]

do you have a Date table joined to your fact table?  if so then that Date table is going to have all dates and will make it contiguous

[v-chuncz-msft]

@JC3,

The standard DAX time intelligence functions do not support operations over non-contiguous periods. You could implement a custom DAX formula.

https://www.sqlbi.com/articles/the-in-operator-in-dax/