Count of Purchase Requests/Purchased Items by Month

[hmeltonPSL]

I am trying to get a count of purchase requests and Purchased Items by month.  I am using a dates table.  Instead of getting the counts for each month I am getting the total counts put as counts for each month.  Only October and November should have numbers...eberything else should be zero.  What am I doing wrong?  Thanks for any help!!

[jonathanbergen]

Can you describe how you have defined the relationship between the Dates table and the Measurestbl Table, and share a screen shot of the model tab?

[hmeltonPSL]

I have not defined a relationship...not sure how to since no date in the Measurestbl.

[jonathanbergen]

You'll need a date in your Measurestbl for each row to get the functionality you want in your report.

Ideally the Measurestble would contain a row for each individual transaction but it could also contain a row per day or per month with totals of PurchaseItems and PurchaseRequests, you would then have a one to many relationship between the Dates table and the Measurestbl on the Date column if working at day level, or a Month column if working at month level.

[Ashish_Mathur]

Hi,

Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Date Table.  To your visual, drag Year, Month from the Date table.

Hope this helps.