Date hierarchy in Slicer

[erhan_79]

Hi There ;

İ need your support about below issue , in the filter slicer , when we add a date formatted column system opens us a date hierarchy option , in that option there is year , quarter , month and day but i would like to add here related week number too.

Is it possible , could you help me pls 

Thanks in advance 

[Icey]

Hi @erhan_79 ,

I agree to create a custom date hierarchy. Please let us know whether the suggestions above could solve your problem.

Best Regards,

Icey

[FrankAT]

Hi @erhan_79 ,

1. It is a good idea to deactivate the checkbox Auto date/time under File > Options ans settings > Options > Current File > Time Intelligence (see figure).
2. Build your one calendar with desired columns.
3. Create your one hierarchie from this calender.
4. Mark this calendar as date table and relate it to your data.

Regards FrankAT

[DataZoe · Accepted solution]

@erhan_79 You can do this with a Date table. From the Modeling ribbon to go "New Table" and use the following:

Delivery Date =
ADDCOLUMNS (
    CALENDAR ( MIN ( Table[Req.dlv.date - Copy] ), MAX ( Table[Req.dlv.date - Copy] ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "MonthNum", MONTH ( [Date] ),
    "WeekNum", WEEKNUM ( [Date] ),
    "WeekOf", [Date] - WEEKDAY ( [Date], 1 ) + 1
)

Then create a relationship between [Date] and [Req.dlv.date - Copy]. You may also want to use the Sort By Column on the [Month] column to be sorted by [MonthNum].

[amitchandak]

@erhan_79 , Create custom Hierarchy

https://stoneridgesoftware.com/creating-hierarchies-in-power-bi/
https://www.youtube.com/watch?v=x6vXVJZ_eTY

Week related -https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[Greg_Deckler]

@erhan_79 - Should be able to add a WEEKNUM column and add it to the hierarchy. You may have to create MONTH, YEAR, DAY, QUARTER columns as well to build an ad-hoc hierarchy instead of relying on the default date hierarchy.