Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
robarbie
Helper I
Helper I

I need help with my DAX formula pertaining to dates

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
 
5 REPLIES 5
AlexisOlson
Super User
Super User

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
Super User
Super User

@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
)

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
)
)
 

@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
    )
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






robarbie
Helper I
Helper I

@jdbuchanan71  can you help with this?  

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.