Forum Discussion

Helper I

3 years ago

Solved

Lastnonblank at row context

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

tamerj1

3 years ago

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

3 years ago

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

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:

Stealth02

Helper I

3 years ago

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

Community Champion

3 years ago

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

Current Yr Surplus = [ItemValue_wSubtotal1] - [ItemValue_wSubtotal2]

Stealth02
Helper I
3 years ago
Super - This works!

Thank you for your help.
Stealth02
Helper I
3 years ago
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:

Forum Discussion

Lastnonblank at row context

Related content

Filter on Visual Row Context

Is row context value in filter context list

Row Context Flag Issue

DAX total vs row context problem

Remove Row context without removing Filter Context