How to get the lastnonblank value when I need to keep several columns in context

[sguzman]

Test file 

Last nonblank column = 
CALCULATE(
SUM('query (18)'[Actuals]),
LASTNONBLANK('query (18)'[Reporting Month].[Month],1),ALL('query (18)'[Equipment],'query (18)'[Factory])
)

Hello,

I have summarised my problem using a few rows. I have some equipments (types) that are used in one or more factories. Every month, there should be a value for defective items (called Actuals in the table underneath.) Not all combinations of equipments and factories have values on every month, thus the need to use the lastnonblank function to get a new column.

I attempted to recreate some suggestions from tutorials of the PBI community, but I cannot manage to get the context right (level of detail.) I have used Tableau so far, and this is my first PBI attempt. In Tableau terms: fixed on every equipment and factory, return the last nonblank value based on all months that have been published. I do not have enable the function to add screenshots or the file, so here is the table. 

I tried to copy my table, but somehow when a cell is blank, the row loses its column and the format is corrupted. To avoid this problem, I have replaced all blank values with zeros. Please, replace back all  blanks to have the actual values and make the exercise relevant (otherwise nothing will be blank.)

Factory	Reporting Month	Equipment	Actuals	Month
Factory 1	2021-01	Equipment 1	0	1/1/2021
Factory 1	2021-01	Equipment 2	32.3	1/1/2021
Factory 1	2021-01	Equipment 3	3	1/1/2021
Factory 1	2021-01	Equipment 4	0	1/1/2021
Factory 1	2021-01	Equipment 5	13	1/1/2021
Factory 1	2021-01	Equipment 6	0	1/1/2021
Factory 1	2021-01	Equipment 7	0	1/1/2021
Factory 1	2021-02	Equipment 1	0	2/1/2021
Factory 1	2021-02	Equipment 2	0	2/1/2021
Factory 1	2021-02	Equipment 4	0	2/1/2021
Factory 1	2021-02	Equipment 5	10	2/1/2021
Factory 1	2021-02	Equipment 6	0	2/1/2021
Factory 1	2021-02	Equipment 7	0	2/1/2021
Factory 1	2021-03	Equipment 1	43	3/1/2021
Factory 1	2021-03	Equipment 2	25.6	3/1/2021
Factory 1	2021-03	Equipment 4	0	3/1/2021
Factory 1	2021-03	Equipment 5	0	3/1/2021
Factory 1	2021-03	Equipment 6	34	3/1/2021
Factory 1	2021-03	Equipment 7	0	3/1/2021
Factory 1	2021-04	Equipment 1	57	4/1/2021
Factory 1	2021-04	Equipment 2	38.5	4/1/2021
Factory 1	2021-04	Equipment 4	64	4/1/2021
Factory 1	2021-04	Equipment 5	0	4/1/2021
Factory 1	2021-04	Equipment 6	0	4/1/2021
Factory 1	2021-04	Equipment 7	0	4/1/2021
Factory 2	2021-01	Equipment 3	43	1/1/2021
Factory 2	2021-02	Equipment 3	87	2/1/2021
Factory 2	2021-02	Equipment 3	23	2/1/2021
Factory 2	2021-03	Equipment 3	0	3/1/2021
Factory 2	2021-03	Equipment 3	0	3/1/2021
Factory 2	2021-04	Equipment 3	0	4/1/2021
Factory 2	2021-04	Equipment 3	0	4/1/2021
Factory 3	2021-01	Equipment 3	76	1/1/2021
Factory 3	2021-02	Equipment 3	0	2/1/2021
Factory 3	2021-03	Equipment 3	68	3/1/2021
Factory 3	2021-04	Equipment 3	0	4/1/2021
Factory 4	2021-01	Equipment 3	30	1/1/2021
Factory 4	2021-02	Equipment 3	87	2/1/2021
Factory 4	2021-03	Equipment 3	0	3/1/2021
Factory 4	2021-04	Equipment 3	0	4/1/2021
Factory 2	2021-01	Equipment 7	0	1/1/2021
Factory 2	2021-02	Equipment 7	0	2/1/2021
Factory 2	2021-03	Equipment 7	0	3/1/2021
Factory 2	2021-04	Equipment 7	0	4/1/2021

Thanks,

Sonia

[DataInsights]

@sguzman,

Here are two ways to achieve this with measures. You can use either LASTNONBLANK or LASTNONBLANKVALUE.

Sum Actuals = SUM ( Equipment[Actuals] )

Last Nonblank =
CALCULATE (
    SUM ( Equipment[Actuals] ),
    LASTNONBLANK ( Equipment[Month], [Sum Actuals] )
)

Last Nonblank Value = 
LASTNONBLANKVALUE ( Equipment[Month], [Sum Actuals] )

[Yerzhan01]

Could you please advise, how i can get correct total amount ? For all factory and equipment.

[sguzman]

Hi DataInsights,

I am unable to replicate your suggestions. This is really my first formula. And due to my fault, I mix the names in the tables with the names in the code. Factory = Entities. You had, depite my mix up, manage to get the values that are needed. I just cannot execute in my file. I get an error regarding "a circular dependency was detected.

This is the first time I upload a file in OneDrive. Hope it works. Can you help  me implement the formula within ?

Thanks,

Sonia

[DataInsights]

@sguzman,

Regarding the question about extending row color banding, you can try adjusting these Formatting properties:

Style
Row headers —> Stepped layout
Values —> Banded row style

[DataInsights]

@sguzman,

You created a calculated column instead of a measure; the measure does not have a circular dependency. Try this measure:

Last Nonblank Value =
LASTNONBLANKVALUE ( 'query (18)'[Month], [SUM Actuals] )

[sguzman]

Hi,

I am so close. Using a measure, as you suggested, I am able to get the right numbers and display them in a table. But I am now facing problems to display this data in the Matrix visual. I am unable to add measures in the row category of the Matrix. I tried to convert the measure to TEXT using FORMAT, but I got again the problem of a circular reference. Can you maybe me throw a few suggestions on how to overcome this ? I updated the PBI file in the One Drive link

Thanks,

Sonia

[DataInsights]

@sguzman,

Measures belong in the Values field well of a matrix, and dimensions belong in the Rows and Columns field wells. Do you have a mockup of the visual you are trying to create?

Also, I noticed that you're not using the measure [SUM Actuals] in the second argument of LASTNONBLANKVALUE. Either use [SUM Actuals] or CALCULATE ( SUM ( 'query (18)'[Actual] ) ). Using the measure [SUM Actuals] as the second argument is simpler, and it results in an implicit CALCULATE, which is necessary for the correct calculation.

[sguzman]

Hi,

I have added the [Sum Actuals] in the calculation. I refreshed the file with a mock up of what I am expecting. I attempted to convert the measure Last Nonblank into text using FORMAT. I thought that as such, I would be able to add it to the matrix (I would that in Tableau.) But it did not work. I now get a row for every month where I have data. I am lost. I appreciate you taking your time to help me iterate. 

Sonia

[DataInsights · Accepted solution]

@sguzman,

Here's a work-around in which you overlay one matrix with another. The visual below is actually two matrices:

The first (top) matrix:

The second (bottom) matrix:

Enable column auto-size for each matrix:

The first (top) matrix should be brought to front:

[sguzman]

Thanks so much. You were very nice to work on the workaround for me. Really appreciate it.

Sonia

[lbendlin]

Thank you for providing sample data. However it does not match your measure definition. Some column headers are spelled differently, and the Entities column is missing from the sample.  In addition you are using a  date hierarchy on the Month field.  I would recommend you use a proper calendar table instead.

[sguzman]

Hi, I apologize for the mix up. I had created a Power BI file with the data, table and formula. I ended up adding a manual table and put easier names to understand without the context of all the explanation I had in the file.  Unfortunately, I forgot to update the names in the code. Hopefuly, you can stull help me. I have updated the code. Factories and Entities are synonyms. And yes, I do have a calendar table.

Thansk,

Sonia