Conversion of Firstnonblankvalue

[jmcph]

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! 

[MFelix · Accepted solution]

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?