Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Brenda Britt]

I have two variables that I want to use, both variables are in tables that are not related to the data table.
The CurrentDate variable is working correctly, but the CurrentPlant is not working correctly. 

Total Stock Value Date =

VAR CurrentDate = MAX(‘Fiscal Periods'[Month End Date])

VAR CurrentPlant = MAX(Plant[Plant])

VAR Result =

CALCULATE(

SUM(‘Inventory Coil Aging'[Total Stock Value]),

FILTER(

‘Inventory Coil Aging’,

‘Inventory Coil Aging'[Posting Date] <= CurrentDate && ‘Inventory Coil Aging'[Plant] = CurrentPlant

)

)

RETURN

Result

——————————
Brenda 
System Data Manager

[Vishesh Jain]

Does your Plant[Plant] column contain dates?

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Brenda Britt]

No, it does not. We look at our data a a company level and then down the the Plant (location) level.

——————————
Brenda Britt
System Data Manager
DeLand FL
3524550343
——————————
——————————————-

[John Thomas]

Brenda,

The part in your “Result” DAX statement is asking…

…  ‘Inventory Coil Aging'[Posting Date] <= CurrentPlant

How can a date value be equal to or less than the CurrentPlant?  To ‘s point, this seems to be part of your issue.  What sort of values are in the “CurrentPlant”?  Text String of Plant Names?  Plant ID Number??

——————————
John Thomas
Huntersville NC
——————————
——————————————-

[Lutz Bendlin]

Remember that variables are impacted by their current filter context. So MAX(plant[plant]) usually refers to the current “row” in a visual, not an actual row in an actual table.

Doesn’t really matter if you use MAX or MIN or AVG etc in such a scenario. You could even use SELECTEDVALUE() if you don’t care about totals.

——————————
Lutz
——————————
——————————————-

[John Thomas]

Brenda,

It’s hard to answer your question if you just say “it’s not working”.  Can you provide more details and a sample pbix file?  What’s not working?  Do you get an error or just the an answer you were not expecting?

Are the variables you are using in a Slicer?  If the tables (“Fiscal Periods” and “Plant”) are not related to the data table, then the MAX will just get you the max value from these tables.  If you add a relationship from these two tables to your data table, then the MAX will return the row from these tables filtered to the current filter context of the data table. 

Another approach may be to use LOOKUPVALUE.  Without an explanation of what’s wrong or what you are getting and some sample data it’s hard to say what the issue may be.

Here’s what LOOKUPVALUE might look like, but again, it’s hard to say without more informaiton.

VAR _CurrentDate = 
    LOOKUPVALUE(
      'Fiscal Periods'[Month End Date], 
      'Fiscal Periods'[Date], 
      MAX('Inventory Coil Aging'[Posting Date])
    )

VAR _CurrrentPlant = 
    LOOKUPVALUE(
      'Plant'[Plant],
      'Plant'[Plant],
      MAX('Inventory Coil Aging'[Plant])
    )

——————————
John Thomas
Huntersville NC
——————————
——————————————-