Forum Discussion

Maya2988's avatar
Maya2988
Helper I
1 year ago
Solved

Need help in Date function

Hi All,   I have a column called YearMonth, which contains last&this year month. Need a help like my YearMonth should show value based on below condition, Every 6th of day of month it must automa...
  • danextian's avatar
    1 year ago

    hI Maya2988 

     

    Try this:

    YearMonth2 = 
    VAR _timezoneOffset = 8  -- Define the timezone offset (adjust as needed, e.g., 8 for UTC+8)
    VAR _utcToday = DATE ( YEAR ( UTCNOW () ), MONTH ( UTCNOW () ), DAY ( UTCNOW () ) )  -- Get the current UTC date from Power BI Service
    VAR _localToday = DATE ( YEAR ( _utcToday ), MONTH ( _utcToday ), DAY ( _utcToday + TIME ( _timezoneOffset, 0, 0 ) ) )  -- Convert UTC date to local time based on the timezone offset
    VAR _completedLast0 = FORMAT ( EDATE ( _localToday, -1 ), "YYYYMM" )  -- Get the YearMonth (YYYYMM format) for the last completed month
    VAR _completedLast1 = FORMAT ( EDATE ( _localToday, -2 ), "YYYYMM" )  -- Get the YearMonth for two months ago
    VAR _completedLast2 = FORMAT ( EDATE ( _localToday, -3 ), "YYYYMM" )  -- Get the YearMonth for three months ago
    VAR _Day6 = DAY ( _localToday ) >= 6  -- Check if the day of the month (local time) is at least 6
    RETURN 
        IF ( 
            IF ( 
                _Day6, 
                YearMonth[YearMonth] IN { _completedLast0, _completedLast1, _completedLast2 }, 
                YearMonth[YearMonth] IN { _completedLast1, _completedLast2 } 
            ), 
            YearMonth[YearMonth]
        )
    

    If you will be refreshing the model in the service then you need to take into account that the service uses UTC and TODAY() might not be necessarily the same as in your timezone thus the need for _timeZoneOffset