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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors