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...
Need Help
  • danextian's avatar
    danextian
    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

     

Maya2988's avatar
Maya2988
Helper I
1 year ago

Hi 

 

I am expecting the below data.

 

As per today's date, need 2 months and after 6th of this month need 3 months

 

 

 

 

Thanks,

Maya

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Maya2988,

is this your expected output ?

 

= Table.AddColumn(#"Changed Type", "ExpectedOutput", each 
        let
            CurrentDate = Date.From(DateTime.LocalNow()),
            CutoffDay = 6,
            MonthsToInclude = if Date.Day(CurrentDate) >= CutoffDay then 3 else 2,
            StartMonth = 202501,
            EndMonth = StartMonth + MonthsToInclude
        in
            if [Date] >= StartMonth and [Date] < EndMonth then [Date] else null
    )

 

Regards,

Vinay Pabbu