Forum Discussion

sureshcu's avatar
sureshcu
Icon for Helper I rankHelper I
8 years ago
Solved

Summarize Table to retrieve 2017 data

Hi,   I have created Table using summarize function. however, I want to restrict the data of 2017 only. Can you please help to retrieve only 2017 data. I have written the following measure.   HC ...
Need Help
  • sureshcu's avatar
    sureshcu
    8 years ago

    You Rock Zubair. A ton thanks for you. It is working fine

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi sureshcu

 

Wrap it with a filter

 

Try this

 

HC & Attrition % =
FILTER (
    SUMMARIZE (
        Append1,
        Append1[Reporting Month],
        "Total HC", [Total EMP],
        "Total Attrition", [Attrition]
    ),
    YEAR ( Append1[Reporting Month] ) = "2017"
)
  • sureshcu's avatar
    sureshcu
    Icon for Helper I rankHelper I
    8 years ago

    Hi Zubair,

     

    I have tried. it is throwing the following error

     

    DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Hi sureshcu

       

      Please remove the apostrophies from 2017.

       

      Instead of "2017" use 2017 only

      • sureshcu's avatar
        sureshcu
        Icon for Helper I rankHelper I
        8 years ago

        Hi Zubair,

         

        Thanks for your help. It is working fine.

         

        And one more clarification. is it possible to add December 2016 Month to this table.

         

         

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      HI sureshcu

       

      Try this

       

      HC & Attrition % =
FILTER (
    SUMMARIZE (
        Append1,
        Append1[Reporting Month],
        "Total HC", [Total EMP],
        "Total Attrition", [Attrition]
    ),
    OR (
        YEAR ( Append1[Reporting Month] ) = 2017,
        AND (
            YEAR ( Append1[Reporting Month] ) = 2016,
            MONTH ( Append1[Reporting Month] ) = 12
        )
    )
)
      • sureshcu's avatar
        sureshcu
        Icon for Helper I rankHelper I
        8 years ago

        You Rock Zubair. A ton thanks for you. It is working fine