Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Hi @Anonymous
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 ) )
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.
Hi @Anonymous
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.
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...
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.
Hi @Anonymous
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
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 | |
Hi @Anonymous
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 ) )
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.
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.
Hi @Anonymous
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |