Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tmears
Helper III
Helper III

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

 

 

datatable2.PNGdatetable.PNG

 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

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@tmears

 

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

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

Hope this helps! Smiley Happy

View solution in original post

5 REPLIES 5
Sean
Community Champion
Community Champion

@tmears

 

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

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

Hope this helps! Smiley Happy

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
Not applicable

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?

 

Anonymous
Not applicable

I am struggling with this.. how do you add the from date as the 1st June each year?

Anonymous
Not applicable

Howdy Jak82 - 

The example provided above  is based on 1st July, if you want to do 1st June, just change the end date to 31/05 like below:

 

 

Booked Sales FYTD = TOTALYTD([Booked Sales],dates[Date], "31/05")

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.