Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |