Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

PREVIOUSMONTH returns wrong results

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?

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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]))

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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