Need help in Date function

[Maya2988]

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

[Anonymous]

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]

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]

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

[danextian · Accepted solution]

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]

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]

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]

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

[Maya2988]

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]

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