Forum Discussion

jmcph's avatar
jmcph
Helper III
5 years ago
Solved

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   B...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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?

MFelix's avatar
MFelix
Super User
5 years ago

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?