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 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

 

 

 

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

4 Replies

  • 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

  • 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.