PREVIOUSMONTH returns wrong results

[Anonymous]

I want to sum the number of 'Activations' in the previous month. So if the report was run and viewed any time during say April (the current month), the 'previous month's figures would be for all of March.

I have a new measure on my fact table:

TotalActivationsPreviousMonth = CALCULATE(sum(fact_PatientActivations[TotalActivations]), PREVIOUSMONTH(dim_Date[D_Date]))

Results are not correct, expecting 101 but I get 57 (highlighted):

Here is the relationship between the date dimension and fact table

And this is the structure of the fact table:

I would expect the formula to sum all TotalActivations for the month of March. But it doesn't seem to be doing that. 

Am I missing something obvious?

[v-deddai1-msft]

Hi @Anonymous ,

Would you please use the following measure :

TotalActivationsPreviousMonth =

CALCULATE (

    SUM ( fact_PatientActivations[TotalActivations] ),

    ALL ( fact_PatientActivations ),

    PREVIOUSMONTH ( dim_Date[D_Date] )

)

If it doesn't meet your requirement, Could you please inform us more detailed information, such as d your sample data(by using OneDrive for Business) if possible? Then we will help you more correctly. Please don't contain any Confidential Information or Real data in your reply or files.

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , your formula seems correct. Just check do you have timestamp in your date.

Change the data type to timestamp and view it in format to have time and check. It it has timestamp.

Date = [Datetime].date

Date = date(year( [Datetime]),month( [Datetime]),day( [Datetime]))