Custom Date Table Drill-down Issue Power BI

2
votes

Using the built-in Power BI date table, you are able to drill down from year -> Qtr -> month effortlessly, as shown below:

  1. enter image description here
  2. enter image description here
  3. enter image description here
  4. enter image description here

The date table used to generate the drill-down figure above: 

DAX Formula: Cal = CALENDAR(MIN(Data[Date]),MAX(Data[Date]))

I would like to maintain this drill-down capability for the following custom date table: 

Dates =
GENERATE (
    CALENDAR ( DATE ( 2016, 10, 1 ), DATE ( 2025, 10, 1 ) ),
    VAR currentDay = [Date]
    VAR year =
        IF ( MONTH ( currentDay ) >= 10, YEAR ( currentDay ) + 1, YEAR ( currentDay ) )
    VAR quarter =
        IF (
            MONTH ( currentDay ) >= 10,
            1,
            IF ( MONTH ( currentDay ) <= 3, 2, IF ( MONTH ( currentDay ) <= 6, 3, 4 ) )
        )
    VAR month =
        IF (
            MONTH ( currentDay ) >= 10,
            MONTH ( currentDay ) - 9,
            MONTH ( currentDay ) + 3
        )
    RETURN
        ROW ( "year", year, "quarter", quarter, "month", month )
)

It seems that the moment I mark "Dates" as the date table, I am unable to implement the built-in drill-down capability. I tried adding a date hierarchy, but cannot seem to control the order of the drill-down. For example, the visuals display month initially, and then "drill-down" to year, and finally to quarter. (this order doesn't make sense to me and I can't seem to change it). I need to be able to go from year -> quarter -> month, as before. I cannot use the default date table because I am using fiscal dates.

This is the result I am getting:

enter image description here enter image description here

Please let me know if anything needs clarification, thank you!

2
powerbidax

2 Answers

0
votes

I am able to sort in custom date table(with different fiscal year), Please find the snapshot attached for your reference.Snapshot

I have used the power query to create a custom date table ( you can find the code here https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-2-fiscal-columns) we just need to change the startdate, enddate and StartOfFiscalYear(custom fiscal month). Try and let me know

0
votes

You have defined your date hierarchy as Date > year > month > quarter, per your screenshot. The order of the fields in the hierarchy defines your drill order. You do not need to define a hierarchy, you can simply drop fields ad-hoc into the field well for any visual. But if you're using a hierarchy, you must define it in the correct order:

year > quarter > month > Date

You can see the order in your hierarchy:

original asker's hierarchy

You can reorder these fields by clicking and dragging them within the hierarchy, or by right clicking and selecting "Move up" or "Move down":

move hierarchy fields