Matrix Measures

[amccallum]

Hi everyone,  

I have a matrix that has percentages by month.  When the month is blank I want to copy the percentage from the prior period into the blank month and continue this until a new percentage is added.   When the new percentage is added I want to copy that and paste it into the blank cells.    

I have tried several different types of measures to do this, but I can't get the measure to recognize the last point in the visual where the data changed.  

Here is my measure so far:

Min Historical Probability = var result = CALCULATE(MIN('Opportunity History'[Probability])*.01

)

var monthContext =calculate(SELECTEDVALUE('Opportunity History'[Start of Month]), ALL('Opportunities'[Aggregate Customer]), all(Opportunities[Master Site])) --gets the month from the visual

var prevprob = CALCULATE(MIN('Opportunity History'[Probability])*.01, all('Opportunity History'[Start of Month]),'Opportunity History'[Start of Month]<=monthContext)

return

if(ISBLANK(result), prevprob, result)

Any advice on how to get the measure to recognize the last result field in the matrix would be much appreciated.  

Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @amccallum ;

Modify it,

1. create a new table.

DATE = FILTER( CALENDAR(MIN('Table'[Start of Month]),MAX('Table'[Start of Month])),DAY([Date])=1)

2.create a measure.

Measure = 
var _lastdate=CALCULATE(MAX('Table'[Start of Month]),FILTER(ALL('Table'),[Opportunityld]=MAX('Table'[Opportunityld])&&[Start of Month]<=MAX('DATE'[Date])))
return CALCULATE(MAX('Table'[Min Prob]),FILTER(ALL('Table'),[Start of Month]=_lastdate&&[Opportunityld]=MAX('Table'[Opportunityld])))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amccallum]

Thank you for your help.  This worked.

[v-yalanwu-msft]

Hi, @amccallum ;

First I create a table about date.

DATE = SUMMARIZE('Table',[Start of Month])

Then create a measure.

Measure = 
var _lastdate=CALCULATE(MAX('Table'[Start of Month]),FILTER(ALL('Table'),[Opportunityld]=MAX('Table'[Opportunityld])&&[Start of Month]<=MAX('DATE'[Start of Month])))
return CALCULATE(MAX('Table'[Prior Min Prob]),FILTER(ALL('Table'),[Start of Month]=_lastdate&&[Opportunityld]=MAX('Table'[Opportunityld])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amccallum]

Thank you for your response, but this isn't quite right.  Your answer spreads the prior minimum but doesn't take into account the current minimums which exist in certain periods.  If I have the below example, I need to take the minimum in the month and spread it into the blanks of the matrix until a new minimum is found for a month.  Then the new minimum needs to be spread until another comes along.  

I have tried to modify your measure, but it overrides the minimum probability for any month after the very first. 

Measure =

Var minprob = min('Table'[Min Prob])

var _lastdate=CALCULATE(MAX('Table'[Start of Month]),FILTER(ALL('Table'),[Opportunityld]=MAX('Table'[Opportunityld])&&[Start of Month]<=MAX('DATE'[Start of Month])))

return

if(not(isblank(minprob)), minprob, CALCULATE(MAX('Table'[Prior Min Prob]),Filter(All('Table'),[Start of Month]=_lastdate&&[Opportunityld]=MAX('Table'[Opportunityld]))))

Any help would be appreciated.

Thanks,

Alicia

[amccallum]

Thank you for your reply.  

I have two tables that make up the matrix.  The first is a date table with all the dates from 1/1/2020 to 12/31/2030.  The second is the data table used for the matrix that gives information by id, date, min probability, prior min probability, index, and the index aggregated by 1.  The data table is missing date information for each id.  See below sample.  

I am creating the matrix on the min probability field.  What I need to do is spread the min probability across the months where the data is missing or the min probability has not changed.  For example:  

Is this possible?

Thanks.

[v-yalanwu-msft]

Hi, @amccallum ;

I have encountered this problem. Sometimes the blank in matrix is not blank in table, or there is no value at all, so it is necessary to establish a table with no relationship.
Not sure if it's the same according to your information. Or can you share a simple sample?It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.