Forum Discussion

albatross19's avatar
albatross19
Frequent Visitor
5 years ago
Solved

Creating a column from a Measure

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:

CampaignMail Drop DateQuantity
August2020-08-28 
October2020-10-05 
November2020-11-15 

 

The table that I am getting my call volume looks like this:

Datephonenumberquality call
2020-09-0980011111111
2020-09-0980011111110
2020-09-1080011111111
2020-10-0680011111120
2020-10-0680011111121
2020-10-0680011111110
2020-11-1880011111111

 

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! 

 

Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

     

3 Replies