Forum Discussion

amccallum's avatar
amccallum
Regular Visitor
4 years ago
Solved

Matrix Measures

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 perc...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
4 years ago

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's avatar
amccallum
Regular Visitor
4 years ago

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