Forum Discussion

AlanP514's avatar
AlanP514
Post Patron
3 years ago
Solved

Dynamic Calendar year

Hai all, I have created a calendar table by using this Dax

Here I want to show the "This year" text in the year field 
The requirement is I have a slicer like this it will show this text every year dynamically (ie. If the current year is 2022 = the YEAR Column should show " this year", if it is 2023 it should show "this year "), Down I am attaching the Dax  whichever I used in power bi as the master calendar

Dim_Calendar =
var _FromDate= MIN ( 'Fact_'[Month Start Date])
var _ToDate= Max ( 'Fact_'[Month Start Date])

var _Today=TODAY()

return
ADDCOLUMNS(
    CALENDAR(_FromDate,_ToDate)
    ,"Year",YEAR([Date])
    ,"Year Start Date",DATE( YEAR([Date]),1,1)
    ,"Year End Date",DATE( YEAR([Date]),12,31)

    ,"Quarter",QUARTER([Date])
    ,"Quarter Name","Q"&FORMAT([Date],"Q")
    ,"Quarter Start Date",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1)
    ,"Quarter End Date",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0)    
    ,"Year Quarter Number",COMBINEVALUES("-",YEAR([Date]),FORMAT( QUARTER([Date]),"00"))

    ,"Month",MONTH([Date])
    ,"Month Name",FORMAT([Date],"MMMM")
    ,"Month Name Short",FORMAT([Date],"MMM")
    ,"Month Start Date",DATE( YEAR([Date]), MONTH([Date]), 1)
    ,"Month End Date",EOMONTH([Date],0)
    ,"Year Month Number",FORMAT([Date],"YYYY-MM")
    ,"Year Month Name",FORMAT([Date],"YYYY-MMM")


Help me to achieve this logic


Thanks 
Alan
Need Help
Show and Tell
Tips and Tricks
  • HoangHugo's avatar
    HoangHugo
    3 years ago

    Hi

    Can not catch you almost, I understand you want to replace the number of current year by text "this year".

    If so, just need creat a new column of year

    Year_2 = if([YEAR column] = YEAR(TODAY()),"This Year",[YEAR column])

     

    then put Year_2 into slicer.

1 Reply

  • HoangHugo's avatar
    HoangHugo
    Solution Specialist
    3 years ago

    Hi

    Can not catch you almost, I understand you want to replace the number of current year by text "this year".

    If so, just need creat a new column of year

    Year_2 = if([YEAR column] = YEAR(TODAY()),"This Year",[YEAR column])

     

    then put Year_2 into slicer.