Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Previous Fiscal YTD DAX Not working

I need to calculate Previous Fiscal Year to date vs Current Fiscal Year to date.

Right now I have Fiscal YTD by: 

Fiscal YTD Net Sales = totalytd(sum(VDC_EDW[Net_Sales_Amount]),'Calendar Table'[Date],"3/31")
^ This calculation works correctly
 
Then I try to get Previous Fiscal Yead to Date with the following:
Fiscal PYTD Net Sales = calculate([Fiscal YTD Net Sales],DATEADD('Calendar Table'[Date],-1,YEAR))
^ This Calculation does not work
 
Given that today is 1/19/2020.
I am seeing 4/1/2019-1/19/2020 for current Fiscal YTD.(Which is what I need)
I would expect to see 4/1/2018-1/19/2019 for previous fiscal year to date but I am not getting that result.
 
 
Need Help
Show and Tell
  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    Please try this..

     

    Fiscal YTD Net Sales = CALCULATE(sum(VDC_EDW[Net_Sales_Amount]),DATESYTD('Calendar Table'[Date],"3/31"))
     
    Then to get Previous Fiscal Yead to Date with the following:
    Fiscal PYTD Net Sales = calculate([Fiscal YTD Net Sales],SAMEPERIODLASTYEAR('Calendar Table'[Date]))
     
    If this helps, mark it as a solution.
    Kudos are nice too

11 Replies

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Anonymous 

     

    Create a variable and use it for previous year.

     

    Var a = PARALLELPERIOD('Calendar Table'[Date],-1,YEAR)

    Fiscal PYTD Net Sales = totalytd(sum(VDC_EDW[Net_Sales_Amount]),a,"3/31")

     

     

    or 

     

    totalytd(sum(VDC_EDW[Net_Sales_Amount]),PARALLELPERIOD('Calendar Table'[Date],-1,YEAR),"3/31")

     

    If this helps, mark it as a solution.

    kudos are nice too.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi VasTg ,

      Thanks for the quick response.

      I'm not quite sure how to use the first one as I am not well versed in variables.

       

      I tried the second one:

      Fiscal PYTD Net Sales = totalytd(sum(VDC_EDW[Net_Sales_Amount]),PARALLELPERIOD('Calendar Table'[Date],-1,YEAR),"3/31")
       
      The results were the same numbers as Current Fiscal Year to Date up to December 31 and did not include up to the current day. (see below)
       
       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Sorry the image didnt post the first time

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Any reason for not having period end date for last year.

      These should work. Also make sure Date table is marked as Date in Data view

      YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi All,

         

        I have tried all the possible solution mentioned in this Post , to get the YTD for previous year. It is not working for me.

        It seems Date Table is not able to filter the Sales Date. I have used [Date] from Date Table in DAX Query.

        Also, Date Table is marked as Date Table.  There is a 1 to Many relationship between Date Table and Sales Table

         

        Here is how I created Date table

         

        Date =
            VAR MinYear = YEAR ( MIN ( Sales_Invoice_Header[Posting Date] ) )
            VAR MaxYear = YEAR ( MAX ( Sales_Invoice_Header[Posting Date] ) )
            RETURN
            ADDCOLUMNS (
            FILTER (
            CALENDARAUTO( ),
            AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
            ),
            "FY YEAR", IF(MONTH([Date]) >=9 , (YEAR([Date]) + 1), YEAR([Date])),
            "Calendar Year", "CY " & YEAR ( [Date] ),
            "Month Name", FORMAT ( [Date], "mmmm" ),
            "Month Number", MONTH ( [Date] ),
            "Weekday", FORMAT ( [Date], "dddd" ),
            "Weekday number", WEEKDAY( [Date] ),
            "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
        )
         
        Please guide. Thanks a lot 
  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Anonymous 

     

    Please try this..

     

    Fiscal YTD Net Sales = CALCULATE(sum(VDC_EDW[Net_Sales_Amount]),DATESYTD('Calendar Table'[Date],"3/31"))
     
    Then to get Previous Fiscal Yead to Date with the following:
    Fiscal PYTD Net Sales = calculate([Fiscal YTD Net Sales],SAMEPERIODLASTYEAR('Calendar Table'[Date]))
     
    If this helps, mark it as a solution.
    Kudos are nice too
  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    Maybe this measure can help you:

    Measure 2 = 
CALCULATE(
    SUM(Sales[Sales]),
    DATESYTD(SAMEPERIODLASTYEAR(Date[Date]), "3/31")
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.