how to calculate the inventory on hand qty for each month's last day in that calendar year upto only current day

Anything related to PowerPivot and DAX Formuale
Sivasuba1
Posts: 2
Joined: Sat Apr 21, 2018 7:28 pm

how to calculate the inventory on hand qty for each month's last day in that calendar year upto only current day

Postby Sivasuba1 » Mon Jun 25, 2018 12:56 pm

Hi All,

We are using SSAS model and creating DAX functions for few measures.Just need help with the below requirement

We are doing calculation for Net change qty based on history inventory on hand qty and the previous month historic inventory qty for the current calendar year starting from Feb up to current date. Is it possible to include the criteria end of last day of each month from Feb to current month? Currently it is calculating from the current date to 30 days..for example if we have 24 May 2015 qty released as 5...the qty stays the same upto June 24 even though we have 0 qty released from June 1st...


Measure we are using Qty On Hand History - Net Change =
CALCULATE(
[Qty On Hand History] - [Qty On Hand History - Prv Mth],
FILTER('Date','Date'[CalendarYearMonthName] <> "2018-Jan" && 'Date'[FullDate] <= now()))

Could you please help me with this? How to add end of each month to output in this calculation ..

Thanks,
Abs

andredl
Posts: 59
Joined: Wed Sep 23, 2015 9:56 pm

Re: how to calculate the inventory on hand qty for each month's last day in that calendar year upto only current day

Postby andredl » Sat Jun 30, 2018 8:28 pm

Assuming you have a calendar lookup table and a data table that contains the inventory value for each end of month date, and that you have a relationship set up between the date field in the data table and the date field in the calendar, I would recommend something like this:

Qty on hand history:=SUM(Inventory[Invent])
Qty on hand history prev mth:=CALCULATE([Qty on hand history],DATEADD('Calendar'[Date],-1,MONTH))

Net Change:=[Qty on hand history]-[Qty on hand history prev mth]

Andre


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 7 guests