Forum Discussion
Conversion of Firstnonblankvalue
Hi everyone,
I am working with DAX with Power Pivot in excel, but i think it does not support the Firstnonblankvalue formula. I need help on converting this formula that will work on excel
BegInventory = Firstnonblankvalue ( Dateadd ( 'Calendar'[Date] , - 1, DAY ) , [EndInventory] )
whereas the [EndInventory] is EndInventory = Calculate ( Sum ( Inventory ) , Lastnonblank ( 'Calendar'[Date] , Sum (Inventory)))
The [EndInventory] works just fine as I intended, but i cannot think of the otherway around.
I hope you can help me.
Moving to Power BI is currently not an option as of now.
Thank you!
Hi jmcph ,
The syntax FIRSTNONBLANKVALUE is not available on the Excel try the following measure:
= VAR temp_Table = SUMMARIZE ( ALL ( Calendar ); Calendar[Date]; "End_Inventory"; SUM ( Table1[Value] ) ) VAR MAXIMUM_DATE = MAXX ( FILTER ( temp_Table; [Date] < MAX ( [Date] ) && [End_Inventory] <> BLANK () ); [Date] ) VAR Total_Previous_Day = FILTER ( temp_Table; [Date] = MAXIMUM_DATE ) RETURN SUMX ( Total_Previous_Day; [End_Inventory] )This should pick the maximum date before the selected date that has a value and return the ending inventory on that day,
If this does not work can you share a smaple file please?
1 Reply
Hi jmcph ,
The syntax FIRSTNONBLANKVALUE is not available on the Excel try the following measure:
= VAR temp_Table = SUMMARIZE ( ALL ( Calendar ); Calendar[Date]; "End_Inventory"; SUM ( Table1[Value] ) ) VAR MAXIMUM_DATE = MAXX ( FILTER ( temp_Table; [Date] < MAX ( [Date] ) && [End_Inventory] <> BLANK () ); [Date] ) VAR Total_Previous_Day = FILTER ( temp_Table; [Date] = MAXIMUM_DATE ) RETURN SUMX ( Total_Previous_Day; [End_Inventory] )This should pick the maximum date before the selected date that has a value and return the ending inventory on that day,
If this does not work can you share a smaple file please?