Forum Discussion
Issue filtering a FullDate Hierarchy
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:
9 Replies
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 ZhangHey, 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 Zhang
