Forum Discussion

Sul's avatar
Sul
Frequent Visitor
3 years ago
Solved

Fill empty table cells with previous cell value

Hello,


I am having an issue of empty cells in my cohorted matrix table where values do not change from the previous cohorted month to the next.

I would like to have these empty cells show the value in the previous cells to indicate no increment at the given cohort month.


Cohort revenue is a cumulative measure, so the values increase with each subsequent cohort month. When a subsequent month has no increment in cohorted revenue, I would like the value of the previous cohorted month to be repeated until there is a newer cohorted month with an incremented value.

 

Currently, I am getting:

 

The desired result:

 

Here is how I calculate the Cohorted revenue measure:
Cohorted revenue = CALCULATE([Revenue],

            FILTER(

                ALLSELECTED('Purchases'[Cohort month]),

                ISONORAFTER('Purchases'[Cohort month], MAX('Purchases'[Cohort month]),DESC)

            )

)

 

I would appreciate it if anyone could explain to me how I can achieve my desired result.
Thanks. 😊

Need Help
  • johnyip's avatar
    johnyip
    3 years ago

    Sul , try the following:

     

    I just typed the following without testing since I don't quite know the data you are using and cannot test if they are correct.

     

    Cohorted revenue = 

    VAR SelectedMonth = MAX('Purchases'[Cohort month])

    VAR MaxMonth = CALCULATE(MAXX(ALLSELECTED(Purchases'[Cohort month]),Purchases'[Cohort month]),REMOVEFILTERS(Purchases'[Cohort month]))

    VAR Result = CALCULATE([Revenue],FILTER(ALLSELECTED('Purchases'[Cohort month]),'Purchases'[Cohort month] <= SelectedMonth))

     

    RETURN

    IF(MAX('Purchases'[Cohort month])>MaxMonth,BLANK(),Result)

5 Replies

    • Sul's avatar
      Sul
      Frequent Visitor
      3 years ago

      Thanks aj1973  for the solution you suggested but this doesn't solve the issue.
      I'm am getting incorrect values populating the entire table.
      It does not seem to work for this problem.

  • johnyip's avatar
    johnyip
    Solution Sage
    3 years ago

    Sul , try the following:

     

    I just typed the following without testing since I don't quite know the data you are using and cannot test if they are correct.

     

    Cohorted revenue = 

    VAR SelectedMonth = MAX('Purchases'[Cohort month])

    VAR MaxMonth = CALCULATE(MAXX(ALLSELECTED(Purchases'[Cohort month]),Purchases'[Cohort month]),REMOVEFILTERS(Purchases'[Cohort month]))

    VAR Result = CALCULATE([Revenue],FILTER(ALLSELECTED('Purchases'[Cohort month]),'Purchases'[Cohort month] <= SelectedMonth))

     

    RETURN

    IF(MAX('Purchases'[Cohort month])>MaxMonth,BLANK(),Result)

    • Sul's avatar
      Sul
      Frequent Visitor
      3 years ago

      Hello johnyip ,
      Thanks for your help. Your formula seems to work, but it creates another issue. ğŸ˜…
      There empty cells are now correctly filled with the previous max cohorted revenue value, however, it last max value run all the way to the last cohort moth for all rows, including rows that contain values that begun in the first cohorted month.

      What I am getting now:

       

      My desired result:


      I can't seem to figur out why the duplicated values up uptil the last cohort month for the months Jul to Dec.
      Do you have an idea why this occurs?






      • johnyip's avatar
        johnyip
        Solution Sage
        3 years ago

        I think this should be because the data you are using includes every "cohorted month" for each "month-year", even when there is no data.

         

        Or, some other reason (i.e. [Revenue], or the data you used)

         

        Cannot quite tell if I cannot look into the PowerBI file with similar data structure of your data you are using.