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

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

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