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 automatically change,

such as today (Apr 1st 2025) then Year Month should hold only 2 values 202501 (Last complete month Previous) &202502(Last complete month).

  • Suppose post this month 6th (Apr 6th 2025) then year month should hold 3 values 202501 / 202502 / 202503

smilarly next month 6th it should hold 202501 / 202502 / 202503 / 202404

 

 

 

Thanks,

Maya

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

     

9 Replies

  • 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

  • danextian's avatar
    danextian
    Super User
    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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