Check if month in a date column is greater than or less than another month

[Shanil_K]

I have a table with two columns, Allocation Date and POD Date. I would like to check for all orders were the month of the Allocation Date is before the month of the POD date(if the POD date exists). If the POD date exists and the value is true then I want to return the text "Brought forward" else "Not Brought forward".  If the POD date does not exist, I want a 2nd check to check if the month of the Allocation Date is before the current month(based on current date). If true then i want to return the text "Brought forward" else "Not brought forward"

Please help me with a formula for this?

[Anonymous]

Hi @Shanil_K ,

Please try below steps:

1. below is my test table

2. create a measure with below dax formula

Measure =
VAR cur_month =
    MONTH ( TODAY () )
VAR cur_all_date_month =
    MONTH ( SELECTEDVALUE ( 'Table'[Allocation Date] ) )
VAR cur_poddate_month =
    MONTH ( SELECTEDVALUE ( 'Table'[POD Date] ) )
RETURN
    SWITCH (
        ISBLANK ( cur_poddate_month ),
        TRUE (), IF ( cur_all_date_month < cur_month, "Brought forward", "Not brought forward" ),
        FALSE (),
            IF (
                cur_all_date_month < cur_poddate_month,
                "Brought forward",
                "Not brought forward"
            )
    )

3. add a table visual with measure and column

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shanil_K]

Hi v-binbinyu-msft

Many thanks for the response! I have included the code in my dashboard however if I want to visualise the data on month level only "Brought Forward" text comes through in the table.

The first column below is Allocation month(using Date hierarchy) and 2nd column if POD month(using date hierarchy). Row 3 of the table the BF Status should read "Not Brought forward" however it reads "Brought Forward".

Is there something else I am missing?

Regards

Shanil

[Anonymous · Accepted solution]

Hi @Shanil_K ,

Please add two column for the month, don't use Date hierarchy's month.

Create a new measure with below dax formula:

Measure3 =
VAR cur_month =
    MONTH ( TODAY () )
VAR cur_all_date_month =
    SELECTEDVALUE ( 'Table'[Allocation Month] )
VAR cur_poddate_month =
    SELECTEDVALUE ( 'Table'[POD Month] )
RETURN
    SWITCH (
        ISBLANK ( cur_poddate_month ),
        TRUE (), IF ( cur_all_date_month < cur_month, "Brought forward", "Not brought forward" ),
        FALSE (),
            IF (
                cur_all_date_month < cur_poddate_month,
                "Brought forward",
                "Not brought forward"
            )
    )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.