Calculated column [CY_LY_Flag ] -Help needed

[sentsara]

Existing Dataset

DimTable

FactSales 

Based on FactSales.LatestMonth='Y', we need to Flag the calculated column CY_LY_Flag ="Yes" or "No" for the below two condition.

Get the LatestMonth='Y' and mark that as "Yes" 

same time Last year also needs to be flagged as "Yes"

remaining records should be "No"

Expected output: for CY_LY_Flag through Calculated Column

FactSales

BatchDate      CY_LY_Flag

03/01/2021    Yes

02/01/2021    No

02/01/2021    No

02/01/2019    No

12/01/2019    No

11/01/2019    No

03/01/2020    Yes

[daxer-almighty]

@sentsara 

Please do yourself a favour and do not create unnecessary columns in fact tables. The column you're after should belong to DimTable, not your to fact table. Also, latestmonth should be moved to DimTable.

[sayaliredij]

Hi @sentsara 

You can try following DAX for a calculated column

CY_LY_Flag = 

var years = {YEAR(TODAY()), YEAR(TODAY())-1 }

RETURN

IF(MONTH(FactSales[BathDate]) = MONTH(TODAY()) && YEAR(FactSales[BatchDate]) IN years,"Yes","No")

Regards,

Sayali

If this post helps, then please consider Accept it as the solution to help others find it more quickly

[sentsara]

Thanks for your quick reply on this.
we need to consider based on the LatestMonth value 'Y' or 'N' as well.

[v-yiruan-msft · Accepted solution]

Hi @sentsara ,

You can create a calculated column as below:

CY_LY_Flag = 
VAR _maxdate =
    CALCULATE ( MAX ( 'FactSales'[BatchDate] ), ALLSELECTED ( 'FactSales' ) )
RETURN
    IF (
        YEAR ( 'FactSales'[BatchDate] )
            IN { YEAR ( _maxdate ), YEAR ( _maxdate ) - 1 }
                && MONTH ( 'FactSales'[BatchDate] ) = MONTH ( _maxdate ),
        "Yes",
        "No"
    )

Best Regards