I need help with my DAX formula pertaining to dates

[robarbie]

I am in the process of creating a dynamic date axis for a viz.

It works but the several of the selection types for quarter and week need a different date option.   For example my boss doesn't want me to use what is highlighted in red.

In the Filtered DIm Date table (the same that I used at the begining to create the calendar)there is a Start of Quarter option.  How do I use this in the formula?   When I tried to edit the formula in red it won't bring up the filtered dim date table as an option.

CALENDAR(MIN('Filtered Dim Date'[Date]), MAX('Filtered Dim Date'[Date])),

"Visual Date", DATE(YEAR([Date]),QUARTER([Date]),1),

"Type","Quarter",

"Order", 2

),

Thanks,

Rochelle

[AlexisOlson]

Please note that it is not possible for a calculated table to read a slicer value. Calculated tables are only calculated once per time the data is loaded to the model and not every time you change a slicer or report filter.

If I've misunderstood what you're trying to do, feel free to ignore this reminder.

[amitchandak]

@robarbie , I not clear what you are trying create with that formula.

A new table

ADDCOLUMNS(
CALENDAR(MIN('Filtered Dim Date'[Date]), MAX('Filtered Dim Date'[Date])),
"Start Qtr", Startofquarter([Date]),
"Type","Quarter",
"Order", 2
)

[robarbie]

Hi,

This was a small portion of the entire formula.  I need to figure out how to use a different data selection where it says Visual Date.  In the same table that I used to create the calendar Filtered Dim Date there are options for Quarter and Start of Week.  I need to figure out sub these for what I have currently.

"Visual Date", DATE(YEAR([Date]),QUARTER([Date]),1),  How do I change this to pull from the Filtered DIm Date Quarter option?

Dynamic Date Selections =

UNION(

ADDCOLUMNS(

CALENDAR(MIN('Filtered Dim Date'[Date]), MAX('Filtered Dim Date'[Date])),

"Visual Date", [Date],

"Type","Year",

"Order", 1

),

ADDCOLUMNS(

CALENDAR(MIN('Filtered Dim Date'[Date]), MAX('Filtered Dim Date'[Date])),

"Visual Date", DATE(YEAR([Date]),QUARTER([Date]),1),

"Type","Quarter",

"Order", 2

),

ADDCOLUMNS(

CALENDAR(MIN('Filtered Dim Date'[Date]), MAX('Filtered Dim Date'[Date])),

"Visual Date", DATE(YEAR([Date]),MONTH([Date]),1),

"Type","Month",

"Order", 3

),

ADDCOLUMNS(

CALENDAR(MIN('Filtered Dim Date'[Date]), MAX('Filtered Dim Date'[Date])),

"Visual Date", DATE(YEAR([Date]),WEEKDAY([Date]),1),

"Type","Week",

"Order", 4

),

ADDCOLUMNS(

CALENDAR(MIN('Filtered Dim Date'[Date]), MAX('Filtered Dim Date'[Date])),

"Visual Date", [Date],

"Type","Day",

"Order", 5

)

)

[PaulDBrown]

@robarbie 

To get the first day of the year, quarter, month, week, and the date

Try:

Dynamic Date Selections =
UNION (
    ADDCOLUMNS (
        CALENDAR (
            MIN ( 'Filtered Dim Date'[Date] ),
            MAX ( 'Filtered Dim Date'[Date] )
        ),
        "Visual Date", STARTOFYEAR[Date],
        "Type", "Year",
        "Order", 1
    ),
    ADDCOLUMNS (
        CALENDAR (
            MIN ( 'Filtered Dim Date'[Date] ),
            MAX ( 'Filtered Dim Date'[Date] )
        ),
        "Visual Date", STARTOFQUARTER[Date],
        "Type", "Quarter",
        "Order", 2
    ),
    ADDCOLUMNS (
        CALENDAR (
            MIN ( 'Filtered Dim Date'[Date] ),
            MAX ( 'Filtered Dim Date'[Date] )
        ),
        "Visual Date", STARTOFMONTH[Date],
        "Type", "Month",
        "Order", 3
    ),
    ADDCOLUMNS (
        CALENDAR (
            MIN ( 'Filtered Dim Date'[Date] ),
            MAX ( 'Filtered Dim Date'[Date] )
        ),
        "Visual Date",
            [Date] - WEEKDAY[Date] + 1,
        "Type", "Week",
        "Order", 4
    ),
    ADDCOLUMNS (
        CALENDAR (
            MIN ( 'Filtered Dim Date'[Date] ),
            MAX ( 'Filtered Dim Date'[Date] )
        ),
        "Visual Date", [Date],
        "Type", "Day",
        "Order", 5
    )
)

[robarbie]

@jdbuchanan71  can you help with this?