Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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!
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.