Forum Discussion

sentsara's avatar
sentsara
Helper II
5 years ago
Solved

Calculated column [CY_LY_Flag ] -Help needed

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

sayaliredij's avatar
sayaliredij
Solution Sage
5 years ago

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's avatar
sentsara
Helper II
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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