Calculate columns for fiscal year aggregations

[MarkPalmberg]

Two issues for which I'm seeking best practices. Here's my data scenario:

I'd like to:

1. Create new columns, per PledgeID, for sum of payments by fiscal year. Does it make sense to create a new table to hold this data? Do I have to create each column (measure) separately, or is there a way to iterate through the InstallmentFY column to generate the new aggregated columns?
2. In my table, then, I'd like to display only the fiscal year columns for the 3 years prior to and from the current date. I assume this would take the form of a Page level filter?

Thanks very much for any thoughts you may have on the matter.

[v-shex-msft · Accepted solution]

HI @MarkPalmberg ,

#1, You can try to use following calculated column to achieve your requirement.

Sum of Current FY =
CALCULATE (
    SUM ( Table[InstallmenntAmount] ),
    FILTER (
        ALL ( Table ),
        [PledgeID] = EARLIER ( Table[PledgeID] )
            && [InstallmentFY] = EARLIER ( Table[InstallmentFY] )
    )
)

#2, I'd like to suggest you write a measure to compare current FY and today's FY and return tag. Then drag this measure to visual level filter and keep Y tag records.

Sample:

Tag Measure =
VAR todayFY = 'formula to convert today to FY'
VAR currFY =
    MAX ( Table[InstallmentFY] )
RETURN
    IF ( currFY <= todayFY - 3, "Y", "N" )

Regards,

Xiaoxin Sheng