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

     

Deku's avatar
Deku
Super User
1 year ago

Your question is a little hard to follow but think this is it 

 

YearMonth = 

Offset= If( day( dates[date] ) <= 6, -1,0 )

Return

Format( eomonth( dates[date], offset -2 ), "yyyymm" )

Maya2988's avatar
Maya2988
Helper I
1 year ago

Hi 

 

Thanks for your help.

 

 

Basically in column2, I am expecting only 2 values 202501 /202502 as of today (Apr 1st, 2025)

After this month 6th (apr 6th 2025) I am expecting 3 values in column2 (i.e.,) 202501/202502/202503

 

Thanks,

Maya

  • Deku's avatar
    Deku
    Super User
    1 year ago

    Still as clear as before. Can you provide the expected output

    • 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