Forum Discussion

tmears's avatar
tmears
Icon for Helper III rankHelper III
9 years ago
Solved

Fiscal Year to Date

Dear all

 

Any help would be appriciated.  My fiscal Year starts on the 1st of April, i have created the enclosed

 

 

 I am trying to create a measure which will bring back previous years sales baed on the FISCAL year ie 2015/2016 and 2016/2017

 

any help would be appricated

 

Tim

 

Need Help
  • Sean's avatar
    Sean
    9 years ago

    tmears

     

    FYTD Measure =
TOTALYTD ( SUM ( Table[Column] ), 'Calendar'[Date], "03/31" )

PFYTD  MEASURE =
CALCULATE ( [FYTD Measure], DATEADD ( 'Calendar'[Date], - 1, YEAR ) )

    Hope this helps! :smileyhappy:

5 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    tmears

     

    FYTD Measure =
TOTALYTD ( SUM ( Table[Column] ), 'Calendar'[Date], "03/31" )

PFYTD  MEASURE =
CALCULATE ( [FYTD Measure], DATEADD ( 'Calendar'[Date], - 1, YEAR ) )

    Hope this helps! :smileyhappy:

    • yuveer's avatar
      yuveer
      New Member
      8 years ago

      Hi Sean,

       

      I am not able to succesfully calculate the PFYTD value using your formula above. I have created a date dimension table which is limited to the scope of dates in my fact table. My fiscal year also starts on 1 April and ends on 31 March. It seems that the formula does not go back to the previous FY and still looks at the latest FY.

       

      I am comparing the result i get in Power BI to the PFYTD value which i calculate below. Perhaps i am not comparing like with like? 

       

      Please advise.

       

      Regards,

      Yuveer


      --Previous YTD
      SELECT
      SUM(A.PO_NETORDERVALUE)
      from
      PURCHASE_ORDER A
      LEFT JOIN
      DATES B
      ON
      A.DATE_KEY = B.DATE
      where
      B.FISCALYEAR = '2018'
      AND
      DATE(a.PO_DATECREATEDHEADER) >= '2017-04-01 00:00:00' AND DATE(A.PO_DATECREATEDHEADER) < '2017-04-14 00:00:00'
      LIMIT 1000;

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi All, I am having great trouble with this previous fiscal year to date.

         

        Firstly, my year to date DAX: 

        Booked Sales = SUM(vw_fact_booking[net_total_amt]) 

        Booked Sales FYTD = TOTALYTD([Booked Sales],dates[Date], "30/06")

        Booked Sales PFYTD = CALCULATE([Booked Sales FYTD],DATEADD(dates[Date],-1,YEAR))

         

        However, it does not work.

         

        If I filter on date table from (yyy-mm-dd) '2017-07-01' to '2018-01-13', then the figure is different, very different (like 170% more) than to what shows up for PFTYD run as of today.

         

        Any ideas why?