Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

YTD showing only current year data

Hi , I want to analyze last 5 years data. and I created YTD for that. If am not selecting any year in the slicer, It is showing current year data. Instead of that, I want to show YTD of last 5 years data. Please help on this.

 

Here is my formula

 

VAR LastSale = MAXX( ALL(ACX_OENS[Document Date] ), OENS[Document Date] )
VAR YTDSales = TOTALYTD( [OE], 'Calendar'[Date] )
RETURN
IF( MIN( 'Calendar'[Date] ) <= LastSale, YTDSales, BLANK() )

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    Below formula is working fine, But I want to replace MAX date with Todays date.
    Any idea
    VAR FirstDateSelected = 
        CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
    VAR Result =
        CALCULATE (
            <measure>,
            DATESBETWEEN ( 
                'Date'[Date], 
                FirstDateSelected,
                MAX ( 'Date'[Date] ) 
           )
        )
    RETURN Result

5 Replies

  • Ani1991's avatar
    Ani1991
    Icon for Resolver III rankResolver III
    7 years ago

    Hi Anonymous,

    By YTD of last 5 years do you mean you want to show the sales from 01-JAN-2014 to 11-SEP-2019. If that been the case, you can try the below measure,

    YTDSales = CALCULATE(SUM(Sales), Filter(Tablename, Date >= DATE(YEAR(TODAY())-5,1,1) && Date <= TODAY()))

    Hope this helps!! Kindly let me know for any concerns.

     

    Thanks,

    Ani

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      In year slicer, If I select a specific year, YTD is showing correctly. But If am not selecting any year, then YTD is showing for the last year. Instead of that, I want to show YTD of all the years.

      • Ani1991's avatar
        Ani1991
        Icon for Resolver III rankResolver III
        7 years ago

        Hi Anonymous 

         

        Please find the attached pbix file. 
        Hope it helps!!. If not then please share some screenshots of results and the expected results.

         

        Thanks,

        Ani