Columns in Matrix

[itsmeanuj]

Hi,

I have a workbook that keeps me updated on the items inventory.  here is the link to download the workbook:

https://drive.google.com/file/d/1tPPybuVuEAiSYy0mjFTCOIVyRDQQpdOS/view?usp=sharing

The data is for last 114 weeks, however matrix visual in PBI has a limitation to show only last 100 columns of data. I have a calculated measure -  "Inventory" in the workbook which takes input from columns Dispense and Purchase and shows the current inventory of the items. 

Now to showcase the latest week, I have to filter out the initial few weeks since we have a limitation of 100 columns. when I apply the filter on the week to keep last 100 weeks, inventory shows the incorrect values since it gets calculated using only last 100 weeks and not the entire dataset which is 114 weeks. e.g. pencil Inventory in week 3/4/2022 is 95. However, if I put a filter on weeks to exclude whole year 2020, the inventory shows 35 (which is incorrect)

Is there a way we can show only last 100 weeks but the inventory calculations takes the entire dataset to showcase the correct number. 

[v-zhangti · Accepted solution]

Hi, @itsmeanuj 

You can reach your needs by changing the filters in the calculation.

Measure:

Inventory = CALCULATE(
	calculate(sum(Sheet1[Purchase]))-calculate(sum(Sheet1[Dispense])),
	FILTER(
		ALL(Sheet1[Week]),
		ISONORAFTER(Sheet1[Week], MAX(Sheet1[Week]), DESC)
	)
)

https://docs.microsoft.com/dax/all-function-dax 

The cancellation of partial weeks at this point will not affect the original calculation.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.