Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sul
Frequent Visitor

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:

Sul_0-1675006496387.png

 

The desired result:

Sul_1-1675006496389.png

 

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. 😊

1 ACCEPTED SOLUTION
johnyip
Super User
Super User

@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)



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

View solution in original post

5 REPLIES 5
johnyip
Super User
Super User

@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)



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!
Sul
Frequent Visitor

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:

Sul_0-1675298042838.png

 

My desired result:

Sul_1-1675298106940.png


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?






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.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!
aj1973
Community Champion
Community Champion

Hi @Sul 

Try this and see if it can help you

aj1973_0-1675009987672.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Sul
Frequent Visitor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.