Issue filtering a FullDate Hierarchy

[NielsDecoene]

I have a problem while making a report on a Data Warehouse

I have 4 visuals with calculations that play with Date/Time example:
AffectedArticlesLastTwoMonths = CALCULATE(
SUMX(FILTER(F_Defects_TicketOwner;[Quantity] >= 0); F_Defects_TicketOwner[Quantity]);USERELATIONSHIP(F_Defects_TicketOwner[ContactDateId];D_FilteredDate[DateId]);
DATESBETWEEN(D_FilteredDate[DateFull].[Date];
FIRSTDATE(DATEADD(D_FilteredDate[DateFull].[Date]; -2; MONTH));
LASTDATE(DATEADD(D_FilteredDate[DateFull].[Date]; -2; MONTH))))

In order to get these visually correct:

I display the measures in a stacked column chart with drill level being lowest lvl of data. (X-axis using "DateFull" hierarchy years and month.

My filter is using the custom Hierarchy Slicer with also the hierarchy set on year and month (Fields again DateFull)

Now this works Correctly. Untill i received additional requests:

Firstly, to only see the full months that have passed( only start showing OCT 2016 and DESC) I filtered my Date as required but it still displays in my Hierarchy Slicer.

Secondly i created a flag Last12Months that indicates the last 12 full months, however trying to add this to the hierarchy slicer results in breaking all my visualisations. 

Is there any way i can manipulate the date hierarchy?

Picutre for illustration:

 

[Anonymous]

Hi @NielsDecoene,

Firstly, what are the date values in the DateFull field of D_FilteredDate? Based on my test, when we drag date field into the custom Hierarchy Slicer, all values in the date field will be displayed.

Secondly, which formula do you use to create the Last12Months? I would recommend you follow the instructions in this blog to create measure to show value for last 12 months.

In addition, we will appreciate that if you can post sample data of your scenario for us to analysis.

Thanks,
Lydia Zhang

[NielsDecoene]

Hey, thanks for the reply.

My D_FilteredDate is a calculated table from my original date dimension.

My field DateFull is modified as a Date/Time Type.

My Last12Months is a flag calculated in my D_FilteredDate using:

Last12Months = (IF(D_FilteredDate[DateFull].[Date]>EOMONTH(NOW();-13) && D_FilteredDate[DateFull].[Date]<=EOMonth(NOW();-1);1;0))

I'll try and explain the full calculations behind the graph:

We have the numerator:

AffectedArticlesLastTwoMonths = CALCULATE(
SUMX(FILTER(F_Defects_TicketOwner;[Quantity] >= 0); F_Defects_TicketOwner[Quantity]);USERELATIONSHIP(F_Defects_TicketOwner[ContactDateId];D_FilteredDate[DateId]);
DATESBETWEEN(D_FilteredDate[DateFull].[Date];
FIRSTDATE(DATEADD(D_FilteredDate[DateFull].[Date]; -2; MONTH));
LASTDATE(DATEADD(D_FilteredDate[DateFull].[Date]; -2; MONTH))
))

We have the denominator:

ProductsInvoicedPrev12Monthsv3 = CALCULATE (
SUMX(FILTER(F_Sales;[InvoiceQuantity] >=0);[InvoiceQuantity]);
DATESBETWEEN(D_FilteredDate[DateFull].[Date];
FIRSTDATE(DATEADD(D_FilteredDate[DateFull].[Date];-13;MONTH));
LASTDATE(DATEADD(D_FilteredDate[DateFull].[Date];-2;MONTH))
))

The fraction is:

Market Defects = ('F_Defects_TicketOwner'[AffectedArticlesLastMonth]/('F_Sales'[ProductsInvoicedPrev12Monthsv3]/1000000))

In essence the fraction takes two different data ranges and based upon that it makes it's calculation. 

I hope this might shed some more light into my problem. Getting sample data isn't that easy for me to do. If alas it doesn't solve it, i'll put in some extra work into manufacturing some masked data. 

Tyvm in advance.

[Anonymous]

Hi @NielsDecoene,

What date values does the DateFull field contain? Based on my test, if the DateFull field contains date values from Jan 2016 to Dec 2016, it will show all the values in the slicer once you drag the field into custom Hierarchy Slicer.

In addition, do you create a visual using Market Defects and DateFull field? If that is the case, when you drag Last12Months into another Hierarchy Slicer, how does the visualization perform?

Thanks,
Lydia Zhang

[NielsDecoene]

Hey @Anonymous

As said the D_FilteredDate comes from D_Date (typical date dimension created from stored procedure)

the D_FiltereDate is a piece of D_Date it starts at a specific day in time (somewhere 2011) and it changes dynamically to

the last full month that's come around, so currently we're 09/11/2016 it will contain everything till 31/10/2016.

The graph has 

Market Defects as Value and as Axis Datefull (Year Month)

If i take the working version and i create a second hierarchy slicer also impacting this visual:

It changes the values on each month to something incorrect. 

I will try and make some data during lunchbreak.

[Anonymous]

Hi @NielsDecoene，

I test your scenario using my data, and I can now reproduce your issue when using hierarchy slicer and the issue still occurs when I drag Last12Months into another Hierarchy Slicer.

I make a test using Power BI Desktop built-in slicer and use the slicers to filter the visual, everything works well, you can have another try with built-in slicer and check if your visual performs well.

In a word, I think the issue is more related to the custom hierarchy slicer, I would recommend you directly contact the author to get support. In this link, you can send an email to the author by clicking “Contact Author”.

Thanks,
Lydia Zhang

[NielsDecoene]

Hi @Anonymous

I've been able to reduce the problem to it's core, after modifying some measures with ALL(DateFull) i can keep the calculations showing correctly. However i can not yet filter the hierarchy based on a flag.

Let's say you have a flag [1,0] on your date dimension.  The flag is true on the last 12 full months.

Context you put the DateFull (Date/Time model) in a tabel, you take the hierarchy. 

If you try and filter this table using the option 1 or 0 it will not affect the table what so ever. 
A custom hierarchy does get filtered but my dashboards are not made based on custom hierarchies....

Is it something special with Power Bi's standard hierarchy preventing the filtering from occuring?

Kind regards Niels Decoene

[Anonymous]

Hi @NielsDecoene,

How do you modify your measure with ALL(DateFull) ? In Power BI, ALL() function ignores all slicers and Visual/Page/Report filters. 

Thanks,
Lydia Zhang

[NielsDecoene]

Hey @Anonymous

Because, the measures are based on DateFull:

-We have the measure being sliced by the X-Axis (DateFull)

-We have the graph being filtered by the Slicer 

So that concludes our DateFull being sliced from two points of origin to avoid Blank(s) to a degree we're trying to let the measures run correct if the data being supplied to the graph get's cut off to a certain degree.

An example :

We filter data (Visual level Filter 12 months) that means the only data my measure can access is those 12 months,
but if we have calcs going back in time we're crossing that boundary. Which results in 0 being returned by the measure.
By supplying ALL in the calculations we're giving our reach the access of our entire dimension.

So even if we want to view the data for the month, that's a year a go the measure still has those dates to work with.

I hope i'm being somewhat clear about it.

P.S. I've contacted the author about filtering the last 12 months out of it. It should be by adding my Flag to the values property of the graph. But alas even that does not work he'll look into it and try to push a fix in the next release.

[Anonymous]

Hi @NielsDecoene,

Would you mind sharing me the PBIX file or relevant screenshots via Private message? From Private message, I note that you have a new issue about Date Hierarchy, I test the TOP N filter and cannot reproduce your issue.

Thanks,
Lydia Zhang