cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Count of Monthly Averages Over a Limit

Hello,

 

I have a table with the following columns:

 

Month / Year / Member ID /  Metric A / Monthly Average of Metric A

 

From this, is there a way to create a new table that includes the above columns plus a new column with a count of the number of times the monthly average of Metric A for each Member ID has been over 550 for the prior 5 months before? In other words the coulums in the new table would be Month,  Year,  Member ID,  Monthly Average of Metric A and Count of instances where the monthly average of Metric A for each Member ID is over 550 for that month and 5 months prior.

 

Thank you.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Count of Monthly Averages Over a Limit

Hi @sford 

I found a workaround

Create a column in your table

year-month = FORMAT('Table'[Date],"yyyy-mm")

Then create a new table

Table 2 =
FILTER (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Member],
            'Table'[year-month],
            "avg", AVERAGE ( 'Table'[Metric A] )
        ),
        "rank", RANKX ( 'Table', [year-month],, ASC, DENSE ),
        "flag", IF (
            DATEDIFF ( MAX ( 'calendar'[Date] ), TODAY (), MONTH ) < 5
                && [AVG] > 550,
            1,
            0
        )
    ),
    DATEDIFF ( MAX ( 'calendar'[Date] ), TODAY (), MONTH ) < 5
)

Then create a measure in this new table

Measure =
CALCULATE (
    SUM ( 'Table 2'[flag] ),
    FILTER (
        ALLEXCEPT ( 'Table 2', 'Table 2'[Member] ),
        'Table 2'[rank] <= MAX ( 'Table 2'[rank] )
            && [flag] = 1
    )
)

Capture12.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Community Support
Community Support

Re: Count of Monthly Averages Over a Limit

Hi @sford 

I use DAX to create the column and measures.

If you want to create such column in Power Query, 

create a column using "date.to.text"

Date.ToText([Date], "yyyy-MM")

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Count of Monthly Averages Over a Limit

The answer is almost certainly yes. You could create a new table that uses ADDCOLUMNS that takes your original table and adds the desired column. However, sample data would be required to create and test this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490...

 

That being said, see if my Time Intelligence the Hard Way provides a means to an end.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Count of Monthly Averages Over a Limit

Hello,

 

I should clarify. By " ...create a new table", I meant a Visual in Table format. I have a Visual with Month / Year / Member ID / Average of Metric A. The hangup is with creating the column that has for each Member ID, a total Count of the Monthly Averages  of Metric A that are over 550 for the given month plus the 5 months previous. The link provided for https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490... did not work. But I think I can provide sample data if needed.

 

Thanks very much.

Highlighted
Community Support
Community Support

Re: Count of Monthly Averages Over a Limit

Hi @sford 

I'd like to help you.

You could provide a simple example and expected result for your example.

Then it may be easier to provide a valid solution for you.

 

Best Regards
Maggie

 

Highlighted
Frequent Visitor

Re: Count of Monthly Averages Over a Limit

Hi,   Here is sample data and desired result. Thanks very much.

 

 

Raw Data

    
      
 

Date

Member

Metric A

  
 

6/1/2019

A

100

  
 

6/3/2019

A

400

  
 

6/4/2019

C

600

  
 

6/8/2019

C

800

  
 

7/1/2019

B

300

  
 

7/3/2019

B

100

  
 

7/5/2019

C

400

  
 

8/2/2019

C

700

  
 

8/3/2019

C

500

  
 

8/3/2019

A

400

  
 

8/3/2019

A

800

  
 

8/4/2019

B

400

  
 

8/5/2019

B

200

  
      
 

Desired Output

   
      
 

Month

Year

Member

Monthly Av Metric A

Running Count of Monthly Av. Metric A >550

 

June

2019

A

250

0

 

June

2019

C

700

1

 

July

2019

B

200

0

 

July

2019

C

400

0

 

August

2019

C

600

2

 

August

2019

A

600

1

 

August

2019

B

300

0

      
Highlighted
Community Support
Community Support

Re: Count of Monthly Averages Over a Limit

Hi @sford 

I found a workaround

Create a column in your table

year-month = FORMAT('Table'[Date],"yyyy-mm")

Then create a new table

Table 2 =
FILTER (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Member],
            'Table'[year-month],
            "avg", AVERAGE ( 'Table'[Metric A] )
        ),
        "rank", RANKX ( 'Table', [year-month],, ASC, DENSE ),
        "flag", IF (
            DATEDIFF ( MAX ( 'calendar'[Date] ), TODAY (), MONTH ) < 5
                && [AVG] > 550,
            1,
            0
        )
    ),
    DATEDIFF ( MAX ( 'calendar'[Date] ), TODAY (), MONTH ) < 5
)

Then create a measure in this new table

Measure =
CALCULATE (
    SUM ( 'Table 2'[flag] ),
    FILTER (
        ALLEXCEPT ( 'Table 2', 'Table 2'[Member] ),
        'Table 2'[rank] <= MAX ( 'Table 2'[rank] )
            && [flag] = 1
    )
)

Capture12.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Count of Monthly Averages Over a Limit

Great.

My only question  is regarding creation of the new column using "...year-month = FORMAT('Table'[Date],"yyyy-mm")...". With that formula I get the following error:  "Expression.Error: The name 'FORMAT' wasn't recognized.  Make sure it's spelled correctly." I guess I'm in the Power Query environment and not DAX. Is there a workaround?

 

Many thanks.

Highlighted
Community Support
Community Support

Re: Count of Monthly Averages Over a Limit

Hi @sford 

I use DAX to create the column and measures.

If you want to create such column in Power Query, 

create a column using "date.to.text"

Date.ToText([Date], "yyyy-MM")

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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