Forum Discussion
Row level measure syntax: (Current year data) minus (SAMEPERIODLASTYEAR data) * Quantity
Hi,
Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.
Hi,
Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.
I have a few more if you can help Ashish_Mathur or Thennarasu_R
Re: my previous solultion, I also want to work out:
Lost Volume = if(current year volume=0 & prior year volume>0), prior year sales)
My code works at a row level, but of course fails at the total:
Lost Volume = if([quantity]=0 && [quantity_LY]>0,sumx(values(AllSalesData[invoice_date].[Date]),-[revenue_LY]),0)
When I try to wrap this in a sumx(values(... to get the total and rows to work, it does not work
Hoping you can help there too?
Mark
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])Amazing, works, really appreciate it!
A few bugs to work out on my side, but the syntax works very well!
Mark
You are welcome. If my previous reply helped, please mark that reply as Answer.