0

I have a Month Column with the Month Field populated for each line for the 100K of lines of data I have.

I need to count the amount of times the Month Field is populated in the Previous Month (Period).

I also need to count the total amount of times the Month Field is populated in the Previous 11 months as well.

This is a rolling count for each months reporting that I do..

table name: 'ws pds' and field name [Month Tagged]

Dave Conva
  • 1
  • 1
  • Share a sample of your data and the expected results. – alejandro zuleta May 31 '16 at 13:33
  • So I have 100K of lines in total and 60K of them are for months in the last 11 months - how do I just do a count on the [Month Tagged] column to get the expected result of 60K. So I see the measure in DAX to be something like current month - 11 to continually look at the past 11 months. – Dave Conva Jun 02 '16 at 22:02
  • You can edit your question and include tha tables with ten or less rows of sample data. Also a clear example of what you are expecting could be useful. – alejandro zuleta Jun 02 '16 at 22:14

1 Answers1

0

You can utilize the powerful time intelligence functions in DAX such as PARRALLELPERIOD to look at values from previous months. But in order to make use of these functions you need to create a calendar/date entity. Mark that entity as a Date table. And join to it by date from your "ws pds" table. The Date dimension should span the timeframe of your date with a continuous list of dates, one row per day.

Then your measure could look like this:

PreviousMonthCount=
CALCULATE (
    COUNTROWS ( 'ws pds' ),
    'ws pds'[Month Tagged] <> BLANK (),
    PARALLELPERIOD ( Calendar[Date], -1, MONTH )
)
Rory
  • 959
  • 10
  • 22