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
