How to calculate 2 months ago in Dax?

[jac88]

Hello, 

I tried to get my average for 2 months ago (Novermber 2021), but I think my calculation doing for last 2 months not for the  November 2021. Can anybody please tell me how to get the 2 months ago average? Basically, I only want the average for November 2021 calendar month.  The below calculation does not give me the right answer. 

PM = CALCULATE(AVERAGE(CSAT_OpsReview[X4_LR_Support_Satisfaction__c]), DATEADD(CSAT_OpsReview[Date], -2, MONTH))

Thank you 

[mahoneypat · Accepted solution]

I see what you are trying to do. Here are two options for getting your result in a card.

1. Not using Time Intelligence (old school) 

Avg 2 Mos Ago New =
VAR todaydate =
    TODAY ()
VAR EOM_Minus2 =
    EOMONTH ( todaydate, -2 )
VAR SOM_Minus2 =
    EOMONTH ( todaydate, -3 ) + 1
RETURN
    CALCULATE (
        AVERAGE ( 'Sample'[X4_LR_Support_Satisfaction__c] ),
        'Calendar'[Date] >= SOM_Minus2
            && 'Calendar'[Date] <= EOM_Minus2
    )

2. Using the Date table in the link I provided before with Months From Today column.

Avg 2 Mos Ago Alt =
CALCULATE (
    AVERAGE ( 'Sample'[X4_LR_Support_Satisfaction__c] ),
    'Date'[MonthsFromNow] = -2
)

Pat

[v-kkf-msft · Accepted solution]

Hi @jac88 ,

You need to limit the maximum date to the current date, like this.

Average 2 Months Ago = 
VAR permonth =
    CALCULATE (
        AVERAGE ( 'Sample'[X4_LR_Support_Satisfaction__c] ),
        DATEADD ( 'Calendar'[Date], -2, MONTH )
    )
VAR total =
    CALCULATE (
        AVERAGE ( 'Sample'[X4_LR_Support_Satisfaction__c] ),
        DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), -2 ), -1, MONTH )
    )
RETURN
    IF ( ISFILTERED ( 'Calendar' ), permonth, total )

Average 3 Months Ago = 
VAR permonth =
    CALCULATE (
        AVERAGE ( 'Sample'[X4_LR_Support_Satisfaction__c] ),
        DATEADD ( 'Calendar'[Date], -3, MONTH )
    )
VAR total =
    CALCULATE (
        AVERAGE ( 'Sample'[X4_LR_Support_Satisfaction__c] ),
        DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), -3 ), -1, MONTH )
    )
RETURN
    IF ( ISFILTERED ( 'Calendar' ), permonth, total )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

I agree you should add a Date table. Here is another option. It includes a Months from Today column that updates with each refresh, so you can use Date[Months From Today] = -2 to get two months ago. Time intelligence is still an option. This table also includes text Date dimensions that do not require Sort By columns (e.g., YearMonthShort).

No Sort Date Tables! – Hoosier BI

Pat

[jac88]

@aj1973  & @parry2k 

Thank you all for quick response. I can try adding date table, but do you think 

DATEADD(CSAT_OpsReview[Date], -2, MONTH))

above dax will only give me November 2021? 

Thank you

[aj1973]

@jac88 

Are you going to try to add a Dimension date table to your model? then we can answer your question if you don't get what you want. Besides if you want a resolution to your issue to be solved more faster then a Sample of your Model would great to share

[jac88]

@aj1973  @parry2k  @mahoneypat 

Hello, 

Thank you so much for your time, I have added theDate table still the same issue. Not sure what I am doing wrong. I have attached sample data, Please take a look at it and please tell me what I am doing wrong. I have manual filter KPI which is the correct value, I also added my dax calculation as well. 

https://drive.google.com/file/d/1pscsFA7pvt6Ed0Jvbw0m01Vl_weGUD0w/view?usp=sharing 

Thank you so much 

[mahoneypat]

Looked at your file, and your measure seems to be working correctly. Are you not seeing same?

Note - although I don't think it is causing an issue, you should make three changes to your model.

1. Turn off Auto Date/Time in the Options (since you have a proper Date table now)

2. Change the data type of both your Calendar and the data table to Date (not DateTime). The calendar one can be done in the Data View, but the data table needs to be changed in the query editor for that table.

3. Mark your table as a Date table.

Pat

[jac88]

Hi @mahoneypat 

Thank you so much for taking look at my file. I did what you suggested but still showed my 2 months ago average as 9.21 which is not correct. I want this as KPI (Card) 

If you see the above screenshot, the average number should be for 2 months ago which December 2021 is 8.89. I am not getting that number on my card. 

Can you please help me to get that or tell me why is not 8.89 instead of 9.21? This little confusing  

Thank you so much

[mahoneypat · Accepted solution]

I see what you are trying to do. Here are two options for getting your result in a card.

1. Not using Time Intelligence (old school) 

Avg 2 Mos Ago New =
VAR todaydate =
    TODAY ()
VAR EOM_Minus2 =
    EOMONTH ( todaydate, -2 )
VAR SOM_Minus2 =
    EOMONTH ( todaydate, -3 ) + 1
RETURN
    CALCULATE (
        AVERAGE ( 'Sample'[X4_LR_Support_Satisfaction__c] ),
        'Calendar'[Date] >= SOM_Minus2
            && 'Calendar'[Date] <= EOM_Minus2
    )

2. Using the Date table in the link I provided before with Months From Today column.

Avg 2 Mos Ago Alt =
CALCULATE (
    AVERAGE ( 'Sample'[X4_LR_Support_Satisfaction__c] ),
    'Date'[MonthsFromNow] = -2
)

Pat

[parry2k]

@ as @aj1973 suggested, when you are using time intelligence function, you need a date dimension in your model. As a best practice, always add a date dimension to your model. You can add one using my blog post here, and then change your measure to use date from date dimension instead of from transaction table.

Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[aj1973]

Hi @jac88 

DATEADD is a Time Intelligence function, it works better with date table

CSAT_OpsReview[Date] migth have some gaps.