Solved: Re: Flag whether or not ID appears in prior calend...

threw001 · ‎08-25-2024

Hello,

I have a table like the below, I would like to add some additional columns that flag (In prev month? & In next month?)-

a) If this record appeared in the prior month?

B) If this record appreared in the next month?

*Please note Month-Year field is a Date type field

Thank you so much for your help 🙂

Month-Year	ID	In prev month?	In next month?
Feb-2024	001	No	Yes
Mar-2024	001	Yes	Yes
Apr-2024	001	Yes	No
Feb-2024	002	No	No
Feb-2024	003	No	Yes
Mar-2024	003	Yes	No

Irwan · ‎08-25-2024

hello @threw001

i assumed you only have 'Month-Year' and 'ID' column as your original table. Additionally, i assumed you want to make both 'In Prev Month?' and 'In Next Month?' as column (not measure).

create a new calculated column for 'In Prev Month?' with following DAX.

In Prev Month? = 
var _Value = 
MAXX(
    FILTER(
        'Table',
        'Table'[Month-Year]<EARLIER('Table'[Month-Year])&&
        'Table'[ID]=EARLIER('Table'[ID])
    ),
    'Table'[Month-Year]
)
Return
IF(
    not ISBLANK(_Value),
    "Yes",
    "No"
)

and create a new calculated column for 'In Next Month?' with following DAX.

In Next Month? = 
var _Value = 
MAXX(
    FILTER(
        'Table',
        'Table'[Month-Year]>EARLIER('Table'[Month-Year])&&
        'Table'[ID]=EARLIER('Table'[ID])
    ),
    'Table'[Month-Year]
)
Return
IF(
    not ISBLANK(_Value),
    "Yes",
    "No"
)

Hope this will help.

Thank you.

View solution in original post

Irwan · ‎08-26-2024

hello @threw001

here is the DAX for 'In Prev Month'.

In Prev Month =
var _Value = 
MAXX(
    FILTER(
        'Table 3',
        'Table 3'[Month-Year]<EARLIER('Table 3'[Month-Year])&&
        'Table 3'[ID]=EARLIER('Table 3'[ID])
    ),
    'Table 3'[Month-Year]
)
Return
IF(
    EOMONTH('Table 3'[Month-Year],-1)=EOMONTH(_Value,0),
    "Yes",
    "No"
)

have you changed 'Month-Year' into date format as well?

just incase here is the pbix (see 'Table 3')

https://www.dropbox.com/scl/fi/iu29hsmofc94eb4vjuq7v/Threw001.pbix?rlkey=gk7zsxlpi1yvw9cpvjwhrxf2u&s...

Hope this will help.

Thank you.

View solution in original post

Irwan · ‎08-25-2024

hello @threw001

i assumed you only have 'Month-Year' and 'ID' column as your original table. Additionally, i assumed you want to make both 'In Prev Month?' and 'In Next Month?' as column (not measure).

create a new calculated column for 'In Prev Month?' with following DAX.

In Prev Month? = 
var _Value = 
MAXX(
    FILTER(
        'Table',
        'Table'[Month-Year]<EARLIER('Table'[Month-Year])&&
        'Table'[ID]=EARLIER('Table'[ID])
    ),
    'Table'[Month-Year]
)
Return
IF(
    not ISBLANK(_Value),
    "Yes",
    "No"
)

and create a new calculated column for 'In Next Month?' with following DAX.

In Next Month? = 
var _Value = 
MAXX(
    FILTER(
        'Table',
        'Table'[Month-Year]>EARLIER('Table'[Month-Year])&&
        'Table'[ID]=EARLIER('Table'[ID])
    ),
    'Table'[Month-Year]
)
Return
IF(
    not ISBLANK(_Value),
    "Yes",
    "No"
)

Hope this will help.

Thank you.

threw001 · ‎08-25-2024

Hi @Irwan

My apologies - I just need a deep dive test and it is not behaving as per expected. Most likely because I did not make this clear in the description.

I have a below example. Client ID 015 initially seen in Jun-2024, not seen in Jul-2024 but again in Aug-2024, I would have expected "In Next Month" field to flag as No as the Next Calendar Month is July and not Aug. Similarly I would expect the Aug-2024 entry, column "In Prev month" to flag as No as it is not the prior month, it was in Jun-2024. Is there anyway to adjust the logic to account for this?

Thank you 🙂

Month-Year	Client ID	In Prev month	In Next Month
Jun-2024	015	No	Yes
Aug-2024	015	Yes	No

Irwan · ‎08-25-2024

hello @threw001

did you not get the expected result in your original data?

the DAX seems work in this sample data without any changes in DAX.

In Next Month = 
var _Value = 
MAXX(
    FILTER(
        'Table 2',
        'Table 2'[Month-Year]>EARLIER('Table 2'[Month-Year])&&
        'Table 2'[Client ID]=EARLIER('Table 2'[Client ID])
    ),
    'Table 2'[Month-Year]
)
Return
IF(
    not ISBLANK(_Value),
    "Yes",
    "No"
)

In Prev Month = 
var _Value = 
MAXX(
    FILTER(
        'Table 2',
        'Table 2'[Month-Year]<EARLIER('Table 2'[Month-Year])&&
        'Table 2'[Client ID]=EARLIER('Table 2'[Client ID])
    ),
    'Table 2'[Month-Year]
)
Return
IF(
    not ISBLANK(_Value),
    "Yes",
    "No"
)

Hope this will help.

Thank you.

threw001 · ‎08-25-2024

Hi @Irwan You're DAX example is also not behaving in the same way I would expect it to.

I would expect the first row (June) to report "No" in "In Next Month" column. This is because next month is technically July-2024 and they do not appear. They appear again in August but not in July
I would expect the second row (August) to report "No" in "In Prev Month" column. This is because for it to say yes, they should have appeared in July-2024, not June-2024.

Hope this makes sense?

Thanks

Irwan · ‎08-25-2024

hello @threw001

i see, so you have jump in date in your original data.

so we can adjust in the if statement.

please try using these following DAXs.

In Prev Month =
var _Value = 
MAXX(
    FILTER(
        'Table 2',
        'Table 2'[Month-Year]<EARLIER('Table 2'[Month-Year])&&
        'Table 2'[Client ID]=EARLIER('Table 2'[Client ID])
    ),
    'Table 2'[Month-Year]
)
Return
IF(
    EOMONTH('Table 2'[Month-Year],-1)=EOMONTH(_Value,0),
    "Yes",
    "No"
)

In Next Month = 
var _Value = 
MAXX(
    FILTER(
        'Table 2',
        'Table 2'[Month-Year]>EARLIER('Table 2'[Month-Year])&&
        'Table 2'[Client ID]=EARLIER('Table 2'[Client ID])
    ),
    'Table 2'[Month-Year]
)
Return
IF(
    EOMONTH('Table 2'[Month-Year],1)=EOMONTH(_Value,0),
    "Yes",
    "No"
)

Hope this will help.

Thank you.

threw001 · ‎08-26-2024

Hi @Irwan

Thank you so much for your help..

Yes thats correct.

I tried the new code you sent over however the 'In Next Month' calculated column is not behaving as expected.

So I have ID 25 - It appears from January 2024, every month up until October 2024. In the month of April 2024, the 'In Next Month' column is flagging 'No' although it definitely appears in May 2024. Hope you can recreate this..

Thanks

threw001 · ‎08-26-2024

Hi @Irwan

To provide more info please use the below source:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdC7DcAwCIThXagtBfwAexbL+6+RRClC8UtUn664Y29Ru56rWrsUUbUhp3xaURtqRx2ojhqoE3WRmqLiNuNtWQP7Bm1rTtmkKTvxD8tp8a+576vnBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month-Year" = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month-Year", type date}, {"ID", type text}})
in
    #"Changed Type"

In Prev Month = 
var _Value = 
MAXX(
    FILTER(
        'Month-Year Client ID',
        'Month-Year Client ID'[Month-Year]<EARLIER('Month-Year Client ID'[Month-Year])&&
        'Month-Year Client ID'[ID]=EARLIER('Month-Year Client ID'[ID])
    ),
    'Month-Year Client ID'[Month-Year]
)
Return
IF(
    EOMONTH('Month-Year Client ID'[Month-Year],-1)=EOMONTH(_Value,0),
    "Yes",
    "No"
)

In Next Month = 
var _Value = 
MAXX(
    FILTER(
        'Month-Year Client ID',
        'Month-Year Client ID'[Month-Year]>EARLIER('Month-Year Client ID'[Month-Year])&&
        'Month-Year Client ID'[ID]=EARLIER('Month-Year Client ID'[ID])
    ),
    'Month-Year Client ID'[Month-Year]
)
Return
IF(
    EOMONTH('Month-Year Client ID'[Month-Year],1)=EOMONTH(_Value,0),
    "Yes",
    "No"
)

Output Table:

I would expect ID 0015 to be "Yes" "In Next Month" from January 2024 all the way to November 2024.
ID 0017 is working as expected.
ID 0036 is working as expected.
ID 0085 is working as expected.
ID 0096 is working as expected.

Thanks

Irwan · ‎08-26-2024

hello @threw001

i edited my post, please look my post above.

Thank you.

threw001 · ‎08-26-2024

@Irwan - you are a legend. Thank you so much for all your help with this..

"In Next Month" works perfectly now!

I still have issues with "In Prev Month".

I used same source code as above just changed the calculated column with your new code

All lines highlighted in yellow should say "No". For example ID 0017, the first month it appears is February 2024 however "In Prev Month" field, it says "Yes" when it should say "No".

Similarly ID 0017 appears again in June 2024, although 0017 appeared in February, this should still "No" as it was back in February 2024 not May 2024 (actual Prev Month)

Irwan · ‎08-26-2024

hello @threw001

hmm.. not sure why you got different result.

i am literally used the same data from before but added more date as your sample data.

i think you are using Text format on 'ID'.

make sure 'Month-Year' is Date format and 'ID' is Number format (Number format will not show those two zero in front as in your sample data).

Hope this will help.
Thank you.

threw001 · ‎08-26-2024

Hi @Irwan - thank you so much again

I have changed ID to Number type however still the same 😞 Are you able to re-share the calculated "In Prev Month" column you are using please? It may be something I've done..

Thanks

Irwan · ‎08-26-2024

hello @threw001

here is the DAX for 'In Prev Month'.

In Prev Month =
var _Value = 
MAXX(
    FILTER(
        'Table 3',
        'Table 3'[Month-Year]<EARLIER('Table 3'[Month-Year])&&
        'Table 3'[ID]=EARLIER('Table 3'[ID])
    ),
    'Table 3'[Month-Year]
)
Return
IF(
    EOMONTH('Table 3'[Month-Year],-1)=EOMONTH(_Value,0),
    "Yes",
    "No"
)

have you changed 'Month-Year' into date format as well?

just incase here is the pbix (see 'Table 3')

https://www.dropbox.com/scl/fi/iu29hsmofc94eb4vjuq7v/Threw001.pbix?rlkey=gk7zsxlpi1yvw9cpvjwhrxf2u&s...

Hope this will help.

Thank you.

threw001 · ‎08-26-2024

Hi @Irwan - definitely something I did as it works perfectly now! Thank you so so much for all your help and effort.

It is much appreciated

Irwan · ‎08-26-2024

hello @threw001

glad it works as intended.

Thank you.

Irwan · ‎08-26-2024

hello @threw001

is this what you are looking for?

i assumed 'In Prev Month' works for you but here is the DAX just incase.

In Prev Month = 
var _Value = 
MAXX(
    FILTER(
        'Table 3',
        'Table 3'[Month-Year]<EARLIER('Table 3'[Month-Year])&&
        'Table 3'[ID]=EARLIER('Table 3'[ID])
    ),
    'Table 3'[Month-Year]
)
Return
IF(
    EOMONTH('Table 3'[Month-Year],-1)=EOMONTH(_Value,0),
    "Yes",
    "No"
)

as for 'In Next Month', it is my bad, i put wrong DAX (since it is later date, it should be MINX instead of MAXX). so here is the revised DAX.

In Next Month = 
var _Value = 
MINX(
    FILTER(
        'Table 3',
        'Table 3'[Month-Year]>EARLIER('Table 3'[Month-Year])&&
        'Table 3'[ID]=EARLIER('Table 3'[ID])
    ),
    'Table 3'[Month-Year]
)
Return
IF(
    EOMONTH('Table 3'[Month-Year],1)=EOMONTH(_Value,0),
    "Yes",
    "No"
)

Hope this will help.

Thank you.

threw001 · ‎08-25-2024

Absolutely perfect - thank you so much for your help! Works a treat 🙂

Flag whether or not ID appears in prior calendar month and next calendar month

Helpful resources

Fabric Data Days

Power BI Monthly Update - October 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

Fabric Data Days starts November 4th!

Flag whether or not ID appears in prior calendar month and next calendar month

Helpful resources

Fabric Data Days

Power BI Monthly Update - October 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly