Create generic Measure or column table for wording FY, Previous FY, etc..

[cristianml]

Hi,

I need help to create a measure o column table that can give me a some wording depending of each Fiscal year. 

I got the Current FY if this criteria is true:

VAR _MTD = MAX('00_CALENDAR'[Current MTD])="MTD"
VAR _REV = SUM('Revenue - CCI'[REV MTD CFY USD])>0

Then I need to put "Current" on all lines that is that current FY and put "Prior 1" for the previous one and "Prior 2" with the next one and so on.

I have this measure so far ..:

Current / Prior FY =

VAR _MTD = MAX('00_CALENDAR'[Current MTD])="MTD"

VAR _REV = SUM('Revenue - CCI'[REV MTD CFY USD])>0

VAR _CURRENT = IF(AND(_MTD,_REV),CALCULATE(SUM('00_CALENDAR'[Fiscal Year])),BLANK())

RETURN

IF(_MTD && _REV,MAX('00_CALENDAR'[Fiscal Year]),"--")

So at the end I would like to have like this : 

is possible ?

At least for previous FY's just to put -1, -2, etc 

Thanks

[v-zhangti · Accepted solution]

Hi, @cristianml 

I simply restore your table data by the method shown below.

Measure:

Current/Prior FY =
IF (
    SELECTEDVALUE ( 'Date'[Fiscal year] ) = YEAR ( TODAY () ),
    "Current",
    IF (
        SELECTEDVALUE ( 'Date'[Fiscal year] ) < YEAR ( TODAY () ),
        "Prior" & "-"
            & YEAR ( TODAY () ) - SELECTEDVALUE ( 'Date'[Fiscal year] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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