cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkPalmberg Regular Visitor
Regular Visitor

Calculate columns for fiscal year aggregations

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

pledges.PNG

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate columns for fiscal year aggregations

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

Re: Calculate columns for fiscal year aggregations

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |