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.

amccallum's avatar
amccallum
Regular Visitor
4 years ago

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.