Forum Discussion
Need help in Date function
- 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
Hi Maya2988,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
Vinay Pabbu
- Anonymous1 year agoNot applicable
Hi Maya2988,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,
Vinay Pabbu- Anonymous1 year agoNot applicable
Hi @Maya2988,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Vinay Pabbu