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 Year, not Fiscal.

 

this didn't work for me it shows all blank: 

Revenue in first month = calculate([T. revenue],datesbetween('dCalendar'[Calendar Date],minx(allexcept(dCalendar,'dCalendar'[Calendar Year]),'dCalendar'[Calendar Date]),minx(allexcept(dCalendar,'dCalendar'[Calendar Year]),'dCalendar'[Calendar Date] )))
 
I want to get a measure that calculates YTD %growth over the first month of the FYear. 
 
Need Help

3 Replies

  • 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%