CODEWITHSUNDEEP
powerbidax
Bail P
Bail P

Reputation: 281

Filtering rows using PREVIOUSMONTH

I am trying to create a measure that filters rows and returns just the rows from the previous month (so as we are currently in August, return the rows from July).

This is the measure I have at the moment:

Prior months decisions made = 
CALCULATE(
    COUNTROWS(table_name), 
    PREVIOUSMONTH('DIM - Date'[date_worked])
)

(for privacy reasons, I have not included the name of the main table)

Both the DIM - Date table and main table have a 'date_worked' column which I have used to link the two together when merging the queries.

The problem I am having is that the above measure just keeps returning the full data set rather than just the previous months data (July's data). Does anybody know why this is happening?

Upvotes: 0

Views: 214

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Your measure code is fine. But to reflect your expectation, These below are the requirements-

1. You should have a separate date table like "dim_date" with column "date".

2. Your fact table "Table" will have a date column as well like "date_worked"

3. The relation between table "dim_date" and "Table" should have a 1 to many relation using columns dim_date[date] and Table[date_worked]

4. Now, create a Year slicer using column dim_date[Date].[Year]

5. Now, create a Month slicer using column dim_date[Date].[Month]

6. Now, create your measure as below-

Prior months decisions made = 
CALCULATE(
    COUNTROWS(Table), 
    PREVIOUSMONTH(dim_dates[Date])
)

You should have expected output now.

Upvotes: 1

Nikolaj Klitlund Børty
Nikolaj Klitlund Børty

Reputation: 717

according to https://dax.guide/previousmonth/ the result is the previous month in relation to the first dates in input. and it only returns dates which are in your "DIM - Date"

maybe it is because your data is not filtered on the date "DIM - Date" table. If you input the whole date table, it will return the whole table.

The function doesn't know that we are in august, it determines the previous month based on your input.

try and add a month slicer.

Upvotes: 2

Related Questions