Forum Discussion
Maya2988
1 year agoHelper I
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...
- 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
danextian
1 year agoSuper User
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