Creating a column from a Measure

[albatross19]

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! 

[v-kelly-msft · Accepted 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.

 
Best Regards,
Kelly

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

[albatross19]

@v-kelly-msftThank you!

This looks pretty similar to the outcome I was expecting!

[Greg_Deckler]

@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.