sales and inventory reports

[martinl]

I am trying to buld a chart over 52 weeks that show Sales vs Inventory

At a weekly level there is no problem

However when I move out to a monthly view it goes wrong. This is because the Sales can be summed to get an end of month figure but the inventory cannot, it needs to be the Value at last week of each month.

How can I do this or am I asking too much.

In my data set I have another column with an eom flag in the data below it would show as 0 until 22/04/2017 where it would be 1 but I dont know how I can use this or if i need too. Maybe Power BI has this even already built in somewhere and I havent found it yet.

	A	B	C	D
1	Product	Date	Attribute	Value
2	Widget	01/04/2017	Sales	2
3	Widget	01/04/2017	Inventory	8
4	Widget	08/04/2017	Sales	3
5	Widget	08/04/2017	Inventory	9
6	Widget	15/04/2017	Sales	0
7	Widget	15/04/2017	Inventory	9
8	Widget	22/04/2017	Sales	1
9	Widget	22/04/2017	Inventory	7

Excel tables to the web >> Excel Jeanie HTML 4

[v-shex-msft]

Hi @martinl,

>>However when I move out to a monthly view it goes wrong. This is because the Sales can be summed to get an end of month figure but the inventory cannot, it needs to be the Value at last week of each month.

You can try to use WEEKNUM function to get last weeknum of current month, then use this result as condition to summary value.

Sample:

Last Weeknum of current Month = 
var currDate=MAX([Date])
return
WEEKNUM(Date(Year(currDate),Month(currDate)+1,1)-1,1)

Regards,

Xiaoxin Sheng

[Anonymous]

From the way you describe it, I would write these two measures:

Sales := CALCULATE(SUM(MyTable[Value]), MyTable[Attribute] = "Sales")

Inventory := CALCULATE(SUM(MyTable[Value]), MyTable[Attribute] = "Inventory", MyTable[EOM] = 1)

[martinl]

Hi scottsen

Thank you for your reply and I apologise for not responding sooner.

Your solution has aided my quest to learn the tool but doesn’t quite solve my issue.

I have though used it as a solution and decided not to allow users to drill into the chart as this is where it goes wrong.

I liked the idea of users being able to drill into yearly, monthly, quarterly, weekly views and the sales worked wonderfully however the stock does not like this as the stock cannot be cumulatively summed but must always take the latest figure.

[Anonymous]

Because you mentioned "eom flag" I thought my technique would work, but typically handling inventory you just use LASTDATE( ) function, eg:

Inventory := CALCULATE(SUM(MyTable[Value]), MyTable[Attribute] = "Inventory", LASTDATE(Calendar[DateKey]))