Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone!
I want to be able to calculate the maximum number of calls that happen on a given day within a date range. I am using a simple
CALCULATE(COUNT(DNIS))
To obtain the number of calls that come from a specific number on a given day.
The table that I want to add the Max Quantity of calls looks like this:
Campaign | Mail Drop Date | Quantity |
August | 2020-08-28 | |
October | 2020-10-05 | |
November | 2020-11-15 |
The table that I am getting my call volume looks like this:
Date | phonenumber | quality call |
2020-09-09 | 8001111111 | 1 |
2020-09-09 | 8001111111 | 0 |
2020-09-10 | 8001111111 | 1 |
2020-10-06 | 8001111112 | 0 |
2020-10-06 | 8001111112 | 1 |
2020-10-06 | 8001111111 | 0 |
2020-11-18 | 8001111111 | 1 |
To enhance the challenge here, the days to take into account should be contraint by the mail drop date.. How can I create a function or write code to make it happen? We can add an additonal column to the first table with a Mail Drop End Date base on the date of the row below from the Mail Drop Date column.
I really appreciate any insights!
Solved! Go to Solution.
Hi @albatross19 ,
Not sure whether I have caught your meaning,see the method below:
Create 2 columns as below:
Related date = CALCULATE(MIN('Table (2)'[Mail Drop Date]),FILTER('Table (2)','Table (2)'[Mail Drop Date]>EARLIER('Table'[Date])))
Date Quantity = CALCULATE(COUNT('Table'[quality call]),FILTER('Table','Table'[Related date]=EARLIER('Table (2)'[Mail Drop Date])))+0
And you will see:
For the related .pbix file,pls see attached.
Hi @albatross19 ,
Not sure whether I have caught your meaning,see the method below:
Create 2 columns as below:
Related date = CALCULATE(MIN('Table (2)'[Mail Drop Date]),FILTER('Table (2)','Table (2)'[Mail Drop Date]>EARLIER('Table'[Date])))
Date Quantity = CALCULATE(COUNT('Table'[quality call]),FILTER('Table','Table'[Related date]=EARLIER('Table (2)'[Mail Drop Date])))+0
And you will see:
For the related .pbix file,pls see attached.
@albatross19 Sorry, having trouble following this. You do not want to create a column from a measure typically since measures are dynamic and calculated columns are not. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Otherwise, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, 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
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |