Dynamic Date Slicer | Calculated Columns for Current Quarter and Current Year

[Anonymous]

Hi all,

I'm having trouble using DAX to creae a dynamic date column in my date table. I am able to do this in Power Query, but would prefer to just do it in DAX so its a bit more simple for the client to take a look at.

I have my date table created like this:

dimCalendar =

VAR BaseTable = CALENDAR(MIN(JobsDB[End Date]), MAX(JobsDB[End Date]))

Return

ADDCOLUMNS(

BaseTable,

"Year",YEAR([Date]),

"Month",FORMAT([Date], "MMMM"),

"Month Number", MONTH([Date]),

"Calendar Month",FORMAT([Date],"MMMM YY"),

"Month Year", FORMAT([Date],"YYYY MM"),

"Report Date",FORMAT([Date],"YYYY/MM/DD"))

With that being said, how can I create the two calculated columns that will dynamically change to CURRENT YEAR ELSE Return the YEAR & Current Quarter ELSE return quarter? Would appreciate any help.

Thanks!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I think your requirement is to build two dynamic Year and dynamic Quarter to show current value or actual value by selection. Unfortunately, calculated column couldn't show dynamic results like this. I think you can try measure. Measure supports to show dynamic results by selections.

For reference:

Calculated Columns vs Measures

What is the difference between Power BI calculated columns and measures?

Build a table, then create a slicer by this table.

Measures:

Dynamic Year = 
VAR _SELECTION = SELECTEDVALUE(Selection[Selection])
RETURN
IF(_SELECTION = "Actual",YEAR(MAX(dimCalendar[Date])),YEAR(TODAY()))

Dynamic Quarter = 
VAR _SELECTION = SELECTEDVALUE(Selection[Selection])
RETURN
IF(_SELECTION = "Actual",QUARTER(MAX(dimCalendar[Date])),QUARTER(TODAY()))

Result is as below.

Select "Actual":

Select "Current":

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.