Current Month, Previous Month formula

[aaronvincentnz]

Hi, 

I have the following formula(s) which both return the same result, but both results are incorrect (I know the answer I need, hence asking this question).

The formulas are as follows

CurrentMonth = CALCULATE(sum('Strategic NPS'[NPS]), DATEADD('Strategic NPS'[Date], 0, MONTH))

CurrentMonth = CALCULATE(sum('Strategic NPS'[NPS]), FILTER ('Strategic NPS','Strategic NPS'[Date]))

My source data is formatted as mmmm yyyy (or 01/12/20 if I use the dd/mm/yy format). I'm thinking there's something wrong with my source data set up given both the above formulas give me the same answer (I'd like to use the first formula).

I've replicated the source data and the dashboards etc from a colleague so have followed their set up with regards to column names, formats etc, but no luck.

Hoping someone can assist.

Thanks

[v-eqin-msft]

Hi @aaronvincentnz ,

According to my understanding, you want to calculate the sum of each Retailer ,right?

You could use the difference calculated byDATEDIFF([DATE1],[DATE2],MONTH) function to identify current/previous month.

But I'm a little confused about the "Current". If the newest date in table (202012) or today(202101) is current?

previous when current is 202012 =
CALCULATE (
    SUM ( 'Strategic NPS'[NPS] ),
    FILTER (
        'Strategic NPS',
        'Strategic NPS'[Retailer] = MAX ( 'Strategic NPS'[Retailer] )
            && DATEDIFF ( [Date], MAXX ( ALL ( 'Strategic NPS' ), [Date] ), MONTH ) = 1
    )
)

previous when current is 202101 =
CALCULATE (
    SUM ( 'Strategic NPS'[NPS] ),
    FILTER (
        'Strategic NPS',
        'Strategic NPS'[Retailer] = MAX ( 'Strategic NPS'[Retailer] )
            && DATEDIFF ( [Date], TODAY (), MONTH ) = 1
    )
)

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aaronvincentnz · Accepted solution]

Thanks everyone, I figured out why it wasn't working. I needed to add a date filter and filter by 'top 1 latest date'

[AllisonKennedy]

@aaronvincentnz  You need to use a Date table with time intelligence functions such as DATEADD. You should also use the Date table date in your visual or filter. Finally, your Current Month formula typicallly does not need a filter. 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

I suggest using the DATEADD version: 

CurrentMonth = SUM('Strategic NPS'[NPS])

PrevMonth= CALCULATE(sum('Strategic NPS'[NPS]), DATEADD('DimDate'[Date], -1, MONTH))

[aaronvincentnz]

Thanks Allison. I'll have to work through your detail above. Just wondering though why my colleague has been able to get the correct answer when I haven't, despite having set things up (source data wise) the same way they have. Granted, I haven't been able to ask them exactly what they've done, so there might be something behind the scenes that I'm unaware of.

As an FYI, here is a sample of what my data looks like and im expecting the results to return the NPS results from Dec-20 for Retailers A, B and C

Date	Retailer	NPS	Rank
Nov-20	A	2.4	1
Nov-20	B	-16.4	3
Nov-20	C	-4.8	2
Dec-20	A	6	1
Dec-20	B	-22.8	3
Dec-20	C	-5	2