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

daxer-almighty's avatar
daxer-almighty
Solution Sage
5 years ago

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.