Forum Discussion
Helper ILastnonblank 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 - 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]
)
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]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 ResultsAnd Results in Pivot Table:
9 Replies
Stealth02This 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
Community Champion
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.