Forum Discussion

cristianml's avatar
cristianml
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

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

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

data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.