Sum for Last Day of Months

[Nolan]

Hi All,

We have an app that loads daily data that contains our Ending Inventory and Orders for our wholesalers. I currently have a pivot table that sums both of these values by Month. I am trying to figure out a way to display the inventory on just the last day of each specific month.

I currently have -

Row:

Wholesaler

Columns: 

Month = MonthName(Day), Day being our Date field loaded daily (MM/DD/YYYY)

Values

Measures:

Sum(Ending Inventory)

Sum(On Order)

So I want to display Ending Inventory and On Order for these months for just the last day of each specific month. And at the same time for the current month to show the most recent day's inventory. Is this possible?

Thanks in advance for your help

[Digvijay_Singh]

May be try something like this, would need some formatting adjustment I guess to make it work.

Sum({<Day = {"=$(=Date(floor(Monthend(Day)),'MM/DD/YYYY'"}>}Ending Inventory)

For today

Sum({<Day = {"=$(=Date(floor(today(1)),'MM/DD/YYYY'"}>}Ending Inventory)

More efficient way is by creating flags in script to identify end of month and current day indicator and then use them like EOMFlag={1} or Todayflag={1} in the expression

[Nolan]

Thank you for the reply Digvijay, would you be able to expand upon the 'formatting adjustments' that would be necessary for this expression? It currently returns me 0's when I plug it in.

Thanks