cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 & Attrition % = SUMMARIZE(Append1,Append1[Reporting Month],"Total HC",[Total EMP],"Total Attrition",[Attrition])Query.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Summarize Table to retrieve 2017 data

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

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Summarize Table to retrieve 2017 data

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"
)
Try my new Power BI game Cross the River
Highlighted
Helper I
Helper I

Re: Summarize Table to retrieve 2017 data

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.

Highlighted
Super User III
Super User III

Re: Summarize Table to retrieve 2017 data

Hi @sureshcu

 

Please remove the apostrophies from 2017.

 

Instead of "2017" use 2017 only

Try my new Power BI game Cross the River
Highlighted
Helper I
Helper I

Re: Summarize Table to retrieve 2017 data

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.

 

 

Highlighted
Super User III
Super User III

Re: Summarize Table to retrieve 2017 data

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
        )
    )
)
Try my new Power BI game Cross the River
Highlighted
Helper I
Helper I

Re: Summarize Table to retrieve 2017 data

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

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors