Last date of the previous month show on a matrix table

v_mark — Mon, 15 Mar 2021 20:28:35 GMT

Good day,

I am connected through Direct Query and trying to calculate the last date of the previous month for my beginning inventory. The goal was to get all the tickets open at a specific point in time and the close date is NULL and have it viewed on a matrix table. Like this one below.

Month - Year	Begin Inventory #	MTD Inventory #
Jan - 2021	?	267
Feb - 2021	?	363
March - 2021	?	58

The sample data set is here

The logic is:

As of Jan 1st 2021.

Count of all tickets with the status of Open

And the placement date is <=12-31-2020

And the close date is NULL

As of Feb 1st 2021.

Count of all tickets with the status of Open

And the placement date is <=01-31-2021

And the close date is NULL

As of March 1st 2021.

Count of all tickets with the status of Open

And the placement date is <=02-28-2021

And the close date is NULL

Tried to use multiple measurements but it only shows MTD.

I appreciate all the help

Re: Last date of the previous month show on a matrix table

amitchandak — Tue, 16 Mar 2021 03:35:22 GMT

v_mark , With help from the date table

Try openingbalancemonth -https://dax.guide/openingbalancemonth/

Refer to my video how to use: https://www.youtube.com/watch?v=6lzYOXI5wfo

topic Last date of the previous month show on a matrix table in DAX Commands and Tips

Last date of the previous month show on a matrix table

Re: Last date of the previous month show on a matrix table