Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated Measure not working in the Matrix

So I created a calculated measure to count the number of new customers and new customer quotes each month. See logic below. I also have a matrix where I have all the sales people listed and I want to...
Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    Whoops sorry:

    CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER('Quotes Estimates US','Quotes Estimates US'[Job Start Year] = YEAR('Quotes Estimates US'[Customer Created Date])&&'Quotes Estimates US'[Job Start Month] = MONTH('Quotes Estimates US'[Customer Created Date])&&'Quotes Estimates US'[Customer Status] = "A"))
JarroVGIT's avatar
JarroVGIT
Resident Rockstar
6 years ago

Whoops sorry:

CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER('Quotes Estimates US','Quotes Estimates US'[Job Start Year] = YEAR('Quotes Estimates US'[Customer Created Date])&&'Quotes Estimates US'[Job Start Month] = MONTH('Quotes Estimates US'[Customer Created Date])&&'Quotes Estimates US'[Customer Status] = "A"))
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi JarroVGIT ,

 

Sorry I just found an error in the measure, I have modified it, pls see below:

 

1.First you need to create a new  calendar table, using calendarauto (),then create a calculated column, expression is as below:

Ym = FORMAT('Calender'[Date],"mmm yyyy")

 

2.Then  create a measure as below:

 

Measure = COUNTROWS(FILTER(SUMMARIZE(FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Customer Status]="A"),'Quotes Estimates US'[Customer],"MinDate",FORMAT(MIN('Quotes Estimates US'[Customer Created Date]),"MMM YYYY")), [MinDate] in FILTERS(Calender[Ym])))

 

Finally you will see :

 

 

As for measure 2,it also needs to corrected, steps are as below:

 

1.create a calculated column:

Ym = 'Quotes Estimates US'[Job Start Year]*100+'Quotes Estimates US'[Job Start Month]

2.Then create a measure:

 

Measure 2 = 
var c = MAX('Quotes Estimates US'[Ym])
var prem = CALCULATE(MAX('Quotes Estimates US'[Ym]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Ym]<c))
var predis = CALCULATETABLE(DISTINCT('Quotes Estimates US'[Customer]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Ym] = prem),VALUES('Quotes Estimates US'[Customer Status]))
 
return
CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER('Quotes Estimates US',NOT('Quotes Estimates US'[Customer] in predis )))

 

Finally you will see :

 

 

 

For the related .pbix file,pls click here.

 

Best Regards,

Kelly