Lastnonblank at row context

[Stealth02]

Hi,

I’m trying to do the following through dax… For each group (i.e. item): if blank, use the lastnonblank value, else use the current value, and then perform a calculation.

(Note: I used dax max - instead of the time intelligence function based on this blog:Optimizing LASTNONBLANK and LASTNONBLANKVALUE calculations - SQLBI).

When items all have the same date as lastnonblank – my measure works properly (see example 1). However, when items have different lastnonblank dates (example 2), my subtotal measure only picks up the lastnonblank of the entire table date context instead of the doing the measure on each individual groups (i.e. items). I suspect I have to either create virtual tables or use the iterator functions (likely on my item_value measure) but I can’t seem to get it to work the way it should. I’m looking for help in doing this in DAX (PowerPivot Excel).

Below you will find examples of the issue and all the elements of my data model (fact and dimension tables, picture of data model and measures) that can be cut and pasted.

Example 1:

SOURCE DATA :

Items	ItemSort	2022-01-01	2022-01-02	2022-01-03	2022-01-04
Item1	1	10	null	null	null
Item2	2	1	null	null	null
Subtotal - Items	3	null	null	null	null

It displays correctly in a pivot table:

Example 2

Items	ItemSort	2022-01-01	2022-01-02	2022-01-03	2022-01-04
Item1	1	10	null	null	null
Item2	2	1	3	null	null
Subtotal - Items	3	null	null	null	null

It does not sum up properly in the pivot table – i.e. it is picking up the last nonblank value of the table (3) for the subtotal (subtotal s/b 13 for columns 2-4), however it is showing the right item values in columns:

Here are the elements of the data model:

Fact:

Items	Date	Value
Item1	2022-01-01	10
Item2	2022-01-01	1
Item2	2022-01-02	3

Dimension

Items	ItemSort
Item1	1
Item2	2
Subtotal - Items	3

Dates

Date

2022-01-01

2022-01-02

2022-01-03

2022-01-04

And a picture of the data model

And here are my three measures –

Measure 1: Item_Value

=IF (

ISEMPTY ( 'Fact' ),

VAR FirstVisibleDate =

MIN ( Dates[Date] )

VAR PreviousDate =

CALCULATE ( MAX ( 'Fact'[Date] ), Dates[Date] < FirstVisibleDate )

VAR PreviousValue =

CALCULATE ( SUM ( 'Fact'[Value] ), Dates[Date] = PreviousDate )

RETURN

PreviousValue,

SUM ( 'Fact'[Value] ) /*Current Value*/

)

Measure 2: Item_Subtotal

=IF (

MAX ( 'Dimension'[ItemSort] ) = 3,

CALCULATE (

[Item_Value],

FILTER (

ALL ( 'Dimension' ),

'Dimension'[ItemSort] < MAX ( 'Dimension'[ItemSort] )

)

)

)

Measure 3: ItemValue_wSubtotal

=SWITCH (

TRUE (),

MAX ( 'Dimension'[ItemSort] ) = 3, [Item_Subtotal],

[Item_Value]

)

[tamerj1 · Accepted solution]

Hi @Stealth02 
It is more clear now but yet some pieces are still missing. I it possible to post a screenshot of your data model (relationship model) in order to complete the picture? However, let me give it a try and ask you to try the following modifications to your measures.

Item_Value =
SUMX (
    VALUES ( Dimension[Items] ),
    CALCULATE (
        IF (
            ISEMPTY ( 'Fact' ),
            VAR FirstVisibleDate =
                MIN ( Dates[Date] )
            VAR PreviousDate =
                CALCULATE ( MAX ( 'Fact'[Date] ), Dates[Date] < FirstVisibleDate )
            VAR PreviousValue =
                CALCULATE ( SUM ( 'Fact'[Value] ), Dates[Date] = PreviousDate )
            RETURN
                PreviousValue,
            SUM ( 'Fact'[Value] ) /*Current Value*/
        )
    )
)

Item_Subtotal =
CALCULATE ( [Item_Value], ALL ( Dimension[Items] ) )

ItemValue_wSubtotal1 =
SWITCH (
    TRUE (),
    MAX ( 'Dimension'[ItemSort] ) = 3, [Item_Subtotal],
    [Item_Value]
)

Now you follow the same for the other fact table. Let's assume [ItemValue_wSubtotal1] belongs to 'Budget' and [ItemValue_wSubtotal2] belongs to 'Expenditures' then your final measure that you will add to the matrix would simply be

Current Yr Surplus = [ItemValue_wSubtotal1] - [ItemValue_wSubtotal2]

[tamerj1]

Hi @Stealth02 

why do you need Subtotal-Items while you have the grand total? Or do you actually have another hierarchy level?

[Stealth02]

This is a sample example, where for simplicity I kept it as a subtotal. But even without considering the subtotal row, the grand total has exactly the same issue as I am displaying in the subtotal item row. 

FYI. For my actual case, I am doing financial statement like reporting, where I am using a approach similar to what is being described in these two videos:  Vancouver Power BI & Modern Excel Usergroup Meeting - Power BI Track Apr 2021 - YouTube and How to Build a Financial Report in Power BI - Bing video but with the added complexity that I have budgets that are only there for some periods - but I want to show these for all periods and use in calculations. Right now I have completed the work around using power query - but I would like that in DAX instead as it will make my modeling more efficient.  

[tamerj1]

@Stealth02 

Not sure how your actual Dimension table looks like but even idlf it contains more than one level, the subtotal item shall not be required. You just need to iterate over the Dimension table to force aditivity such as

Measure 4 = SUMX ( Dimention, [Item_Value] )

and total value shall appear at the grand total cell. 

[Stealth02]

Ok. 

This addresses the "Grand Total", not displaying the right total. Thank you. 

However, as I indicated above, the Subtotal is only as a placeholder for more complex calculations (i.e. it could be item 1 minus item 2) - and the grand total would be turned off in the financial matrix I am preparing. This is the one I am trying to get solved. 

fyi - I tried applying the Sumx solution to the to my Subtotal measure, but still gave me the wrong solution.

[tamerj1]

@Stealth02 

I forgot to mention that the minus items can be handled. However this cannot be broken down into pieces, it has to be one comprehensive solution, therefore complete information is required to provide proper solution.

[Stealth02]

Thank you.

I apologize for the late response - I was away.

Here is more details of my actual model:

• I have two fact tables: a budget and a expenditures table
• The details of the values are being captured in a Value Type Dimension.

In my actual scenario, I am looking to create a financial matrix (pivot table) similar to the one below.

All budget and expenditure items are part of the Value type dimension. However, the Current Yr and prior year Suplus/Shortfall are not part of the value type dimension.

Budget items with the * were nulls that got replaced by the last know budget value.

	Y1	Y2	Y3
Budget Item 1	1	1*	1*
Budget Item 2	2	2	2*
Budget Item X	3	4	5
Total Budget	6	7	8
Expenditures	8	10	12
Total Expenditures	8	9	12
Current Yr surplus/shortfall (Total Budget - Total Expenditures)	(2)	(3)	(4)
Prior Yr surplus/shortfall (Prior yr Total Budget - Prior yr Total Expenditures)		(2)	(3)

In my first approach to tackling this (which was successful) - I created a measure for each of the items of the first column and essentially used those measures as a "custom" dimension. However, I did like the approach as it involved high maintenance as 15+ measures were created in order to populate the above matrix.

In my second approach - I created a dimension table that contained all items above - and connected it to the value type dimension (i.e. as a snowflake dimension).

If I was able to capture the last non blank in my total and surplus/shortfall calculation - I would be able to reduce my measures to 4 (Budget, Expenditures, Current yr surplus/shorfall, Prior yr surplus/shorfall).

Note: I tried filling in the null budget items via power query - and while I was successful - it doubled my refresh time.

So hopefully that you are able to provide some insight that would help me to resolve this via dax…

[tamerj1 · Accepted solution]

Hi @Stealth02 
It is more clear now but yet some pieces are still missing. I it possible to post a screenshot of your data model (relationship model) in order to complete the picture? However, let me give it a try and ask you to try the following modifications to your measures.

Item_Value =
SUMX (
    VALUES ( Dimension[Items] ),
    CALCULATE (
        IF (
            ISEMPTY ( 'Fact' ),
            VAR FirstVisibleDate =
                MIN ( Dates[Date] )
            VAR PreviousDate =
                CALCULATE ( MAX ( 'Fact'[Date] ), Dates[Date] < FirstVisibleDate )
            VAR PreviousValue =
                CALCULATE ( SUM ( 'Fact'[Value] ), Dates[Date] = PreviousDate )
            RETURN
                PreviousValue,
            SUM ( 'Fact'[Value] ) /*Current Value*/
        )
    )
)

Item_Subtotal =
CALCULATE ( [Item_Value], ALL ( Dimension[Items] ) )

ItemValue_wSubtotal1 =
SWITCH (
    TRUE (),
    MAX ( 'Dimension'[ItemSort] ) = 3, [Item_Subtotal],
    [Item_Value]
)

Now you follow the same for the other fact table. Let's assume [ItemValue_wSubtotal1] belongs to 'Budget' and [ItemValue_wSubtotal2] belongs to 'Expenditures' then your final measure that you will add to the matrix would simply be

Current Yr Surplus = [ItemValue_wSubtotal1] - [ItemValue_wSubtotal2]

[Stealth02]

Super - This works! 

Thank you for your help.

[Stealth02 · Accepted solution]

Just for reference - Here is the complete model and measures based on @tamerj1 response above (Raw Tables, Tables loaded to DataModel, DataModel diagram and Measures):

Raw tables transformed through PQ:

RawBudgetTable

Items	ItemSort	2022-01-01	2022-01-02	2022-01-03
Budget - Item1	1	1
Budget - Item2	2	2	2
Budget - Item 3	3	3	4	5

Raw Expenditure Table

Items	ItemSort	2022-01-01	2022-01-02	2022-01-03
Expenditures	1	8	10	12

Transformed Tables - Added to the data model:

Fact Tables:

FactBudget:

Items	Date	Value
Budget - Item1	2022-01-01	1
Budget - Item2	2022-01-01	2
Budget - Item2	2022-01-02	2
Budget - Item 3	2022-01-01	3
Budget - Item 3	2022-01-02	4
Budget - Item 3	2022-01-03	5

FactExpenditures:

Items	Date	Value
Expenditures	2022-01-01	8
Expenditures	2022-01-02	10
Expenditures	2022-01-03	12

Dimension Tables:

Dates:

Date

2022-01-01

2022-01-02

2022-01-03

2022-01-03

DimValueType

Items	ItemSort
Budget - Item1	1
Budget - Item2	2
Budget - Item 3	3
Expenditures	1

dimFinMatrix

Financial Matrix	ItemSort
Budget - Item1	1
Budget - Item2	2
Budget - Item 3	3
Total Budget	4
Expenditures	5
Total Expenditures	6
Surplus/Shortfall - Current Period	7

Diagram of DataModel:

Measures:

ItemValueBudget=
SUMX (
    VALUES ( DimValueType[Items] ),
    CALCULATE (
        IF (
            ISEMPTY ( 'FactBudget' ),
            VAR FirstVisibleDate =
                MIN ( Dates[Date] )
            VAR PreviousDate =
                CALCULATE ( MAX ( 'FactBudget'[Date] ), Dates[Date] < FirstVisibleDate )
            VAR PreviousValue =
                CALCULATE ( SUM ( 'FactBudget'[Value] ), Dates[Date] = PreviousDate )
            RETURN
                PreviousValue,
            SUM ( 'FactBudget'[Value] ) /*Current Value*/
        )
    )
)

ItemValueExpenditures=
SUM(FactExpenditures[Value])

ItemTotalBudget=
CALCULATE([ItemValueBudget],all(dimValueType))

ItemTotal_Expenditures=
CALCULATE([ItemValueExpenditures],all(dimValueType))

SuplusShortfall_Current=
[ItemTotalBudget]-[ItemTotal_Expenditures]

ItemwValue=
VAR MaxItem =
    MAX ( dimFinMatrix[ItemSort] )
VAR Results =
    SWITCH (
        TRUE (),
        MaxItem < 4, [ItemValueBudget],
        MaxItem = 4, [ItemTotalBudget],
        MaxItem = 6, [ItemTotal_Expenditures],
        MaxItem = 7, [SurplusShortfall_Current],
        [ItemValueExpenditures]
    )
RETURN
    Results

And Results in Pivot Table: