Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Row level measure syntax: (Current year data) minus (SAMEPERIODLASTYEAR data) * Quantity

Hi,   I'm new to DAX... I am trying to create a measure that calculates (i.e. calculates row by row, then aggregates):   ( (Current year sales / Current year quantity) - (Prior year sales / Prior...
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Try this measure

Lost volume = SUMX(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(Data[order_no]),DATESBETWEEN('Calendar'[Date],edate(min('Calendar'[Date]),-12),min('Calendar'[Date])-1)),[order_no],"ABCD",[Quantity],"EFGH",[Quantity in PY],"IJKL",[Revenue in PY]),[ABCD]=0&&[EFGH]>0),[IJKL])
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Amazing, works, really appreciate it!

 

A few bugs to work out on my side, but the syntax works very well!

 

Mark

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    You are welcome.  If my previous reply helped, please mark that reply as Answer.