Forum Discussion
Issue filtering a FullDate Hierarchy
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
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.
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 ZhangHey 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.
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