PREVIOUSMONTH returning selected months data rather than previous month

Question

Using a measure, I am trying to return a previous months data. I have a month slicer and when selecting June for example, the measure is returning June's data not May's.

Here is the measure:

Prior months contact rate = 
CALCULATE(
    [Offline Contact Rate], 
    PREVIOUSMONTH('DIM - Date'[date_worked])
)

The offline contact rate measure simply finds the percentage of a number by dividing some filtered rows by the whole data set.

The offline contact rate uses data from the main table and the 'Dim - Date' table is a separate table which is marked as a date table. The two tables are linked using the 'date_worked' column which they both have.

Offline contact rate measure:

Offline Contact Rate =
DIVIDE(
    CALCULATE(
        COUNTROWS('main_table'), 
        FILTER(
            'main_table', 
            'main_table'[worktype]="Offline" 
                && 'main_table'[contact_reason]="Further information"
        )
    ),

    CALCULATE(
        COUNTROWS('main_table'), 
        FILTER(
            'main_table', 
            'main_table'[worktype]="Offline"
        )
    )
)

You have syntax issue in your provided code for [Offline Contact Rate]. Is main_table and FACT - main_table are different table? — mkRabbani
Sorry that was just a typo on the stack overflow post. I have edited this now. The problem is still the same — Bail P

mkRabbani mkRabbani · Accepted Answer · 2020-08-27T11:27:33

You have some missing parenthesis in your code. I have added them with some comments at the end of line.

Offline Contact Rate =
DIVIDE(
    CALCULATE(
        COUNTROWS('main_table'), 
        FILTER(
            'main_table', 
            'main_table'[worktype]="Offline" 
                && 'main_table'[contact_reason]="Further information"
        )
    ), //-- This closing parenthesis is missing 

    CALCULATE(
        COUNTROWS('main_table'), 
        FILTER(
            'main_table', 
            'main_table'[worktype]="Offline"
        )
    ) //-- This closing parenthesis is missing 
) //-- This closing parenthesis is missing

Now, if it still not working as per expectation, just write your 2 FILTER part in the code as below-

FILTER(
    ALL('main_table'), //-- Just added the ALL function
    //-- other conditions as it is now
)

If the issue is still there -Can you check one last thing that you have relation between your table "DIM - Date" and "main_table" using column "date_worked". That has to be a One to Many relation.

PREVIOUSMONTH returning selected months data rather than previous month

1 Answers