Forum Discussion

MuniqueCrespo's avatar
MuniqueCrespo
Icon for Helper I rankHelper I
3 years ago

YTD % Growth when using Fiscal Year

Hello,    How can I find the "Revenue in the first month" when using the Fiscal Year that starts on May 1st of every year? I found a few answers in the Forum but they were all based on Calendar Ye...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi MuniqueCrespo,

I'd like to suggest you use the fiscal year tag field to find out correspond calendar date range of start/end date at first. Then you can use these date values as condition in the formula calculation to limit the calculation ranges.

formula =
VAR currFY =
    MAX ( 'dCalendar'[Fiscal Year] )
VAR currDate =
    MAX ( 'Table'[Date] )
VAR range =
    CALCULATETABLE (
        VALUES ( 'dCalendar'[Date] ),
        FILTER ( ALLSELECTED ( 'dCalendar' ), [Fiscal Year] = currFY )
    )
RETURN
    CALCULATE (
        [T. revenue],
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Date] >= MINX ( range, [Date] )
                && [Date] <= MAXX ( range, [Date] )
                && [Date] <= currDate
        )
    )

Regards,

Xiaoxin Sheng

MuniqueCrespo's avatar
MuniqueCrespo
Icon for Helper I rankHelper I
3 years ago

Hello, 

 

Thank you for your reply. To find the YTD values I have the Formula below which works fine for me. Your formula does the same as below. What I need help with is the find the YTD % Growth.

 

Revenue YTD = if(ISBLANK([T. Revenue]), Blank(), CALCULATE([T. Revenue],DATESYTD(dCalendar[calendar Date],"30/04")))
 
I need the YTD return Calculation. something like: 

YTD Returns Calculation

For example, if an investor’s portfolio was worth $200,000 at the beginning of the Fiscal Year and is currently worth $220,000 in the middle of the Fiscal Year, the year to date return is calculated as 10%.

  • Year to Date (YTD) = [($220,000 – $200,000) ÷ $200,000) = 0.10, or 10%
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI MuniqueCrespo,

    Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng