How to get a calculated column with n position of period

[Anonymous]

Hi everyone,

I'm trying to create a calculated column that would give me the position of a period : n would be the latest, n-1 before the latest, etc...

Here is my script :

position vague = IF(
    Exports[VAGUE]= format([Vague N-2], "mmmm yyyy"),"N-2", if(
        Exports[VAGUE]=format([vague n-1],"mmmm yyyy"),"N-1",if(
            Exports[VAGUE]=format([Vague N], "mmmm yyyy"),"N","-")))

And here are the code of my values "vague n", "vague n-1" and "vague n-2" :

Vague N = MAX(Exports[date vague])

vague n-1 = CALCULATE(MAX(Exports[date vague]),DATEADD(Exports[date vague],-1,MONTH))

vague n-2 = Calculate(MAX('Exports'[date vague]),DATEADD(Exports[date vague],-3,MONTH))

The column "date vague" helps me to do measures based on dates.

The problem is that I'm always getting the result "N" (screen below). Here, décembre 2021 should be N, octobre 2021 should be "N-1" and juillet 2021 should be "N-2":

What can I do to get what I expect ?

Thanks for your help

[v-angzheng-msft]

Hi, @Anonymous 

Try to create measures like below:

_Vague N = CALCULATE(MAX('Table'[Date]),ALL('Table'))

_Vague n-1 = 
// var _max=CALCULATE(MAX('Table'[Date]),ALL('Table'))
// return 
CALCULATE(MAX('Table'[Date]),filter(ALL('Table'),EOMONTH('Table'[Date],0)=EOMONTH([_Vague N],-1)))

_Vague n-2 = 
CALCULATE(MAX('Table'[Date]),filter(ALL('Table'),EOMONTH('Table'[Date],0)=EOMONTH([_Vague N],-2)))

position vague = 
SWITCH(
    TRUE(),
    MAX('Table'[VAGUE])=FORMAT([_Vague N],"mmmm yyyy"),"N",
    MAX('Table'[VAGUE])=FORMAT([_Vague n-1],"mmmm yyyy"),"N-1",
    MAX('Table'[VAGUE])=FORMAT([_Vague n-2],"mmmm yyyy"),"N-2","-"
)

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Anonymous]

Hi,

Unfortunatly your solution doesn't seem to be working. I'm getting my vague N working but vague n-1 gives me a blank column since there's no data in november and vague n-2 will give me october, but vague n-2 should be july. Also the position vague column gives me always N-2 value.

To be more precise, I want a way to calculate automatically the latest month, and the latest month before this one, etc...

here is the screenshot of what I get, using your solution

regards

[amitchandak]

@Anonymous , That will work in a measure , try like this in column

vague n-1 =
var _end_date = eomonth(Exports[date vague],-1)
var _start_date = eomonth(Exports[date vague],-1-1)+1
return
MAXX(filter(Exports[date vague] >= _start_date && Exports[date vague] <=_ _end_date) Exports[date vague])

Additional -1 to keep -n if you need as var