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
bortxs
Frequent Visitor

Previous Fiscal Year To Date & FYTD - not working

Hi there, 

 

I´m struggling with the "Fiscal Year To Date" and "Previous Fiscal Year To Date measures" and I hope you can help me ´m solving the issue... 

 

Look, I have created 4 measures for the FYTD/PFYTD analysis (Our FY starts on the 1st of October [Oct-Sep]) both for the Net Order Value as well as for the Website-Visits:

 

- Fiscal Year To Date Net order Value = TOTALYTD (SUM('DWH'[Net Order Value]);DATE[Date];"31.09")

 TOTALYTD (SUM('DWH'[Net Order Value]);DATE[Date];"31.09")

 

- Previous Fiscal Year to Date  Net order Value CALCULATE(SUM('DWH'[Net Order Value]);DATEADD(DATE[Date];-1;YEAR))

CALCULATE(SUM('DWH'[Net Order Value]);DATEADD(DATE[Date];-1;YEAR))

 

- Fiscal Year To Date Conversion Rate = TOTALYTD(DISTINCTCOUNT('DWH'[Ordernumber]);DATE[Date];"31.09")/TOTALYTD(SUM(VISITS[Sessions]);DATE[Date];"31.09")

TOTALYTD(DISTINCTCOUNT('DWH'[Ordernumber]);DATE[Date];"31.09")/TOTALYTD(SUM(VISITS[Sessions]);DATE[Date];"31.09")

 

 - Previous Fiscal Year To Date Conversion Rate = CALCULATE(DISTINCTCOUNT('DWH'[Ordernumber]);SAMEPERIODLASTYEAR(DATE[Date]))/CALCULATE(SUM(VISITS[Sessions]);SAMEPERIODLASTYEAR(DATE[Date]))

= CALCULATE(DISTINCTCOUNT('DWH'[Ordernumber]);SAMEPERIODLASTYEAR(DATE[Date]))/CALCULATE(SUM(VISITS[Sessions]);SAMEPERIODLASTYEAR(DATE[Date]))

 

I´ve followed other examples for the creation of these measures and they seem to work, but the figures these formulas show are unfortunately wrong. Below you may see an example of what should be shwon and what´s actually shown using these formulas.

 

On the left side (in "green") you´ll find the correct figures I should get... in the right side the values I get... surprisingly I get the figures divided and I cannot understand completely why.... (it´s like the colors are different due to the calendar year... could it be?)

 

 

FYTD Problem.PNG

 

Apart from that, I´ve created a new column for the fiscal years, where I basically say:

IF(MONTH(VISITS[Date])>9;"FY"&YEAR(VISITS[Date])+1;"FY"&YEAR(VISITS[Date]))

 This has been applied to the "DWH" table (where all order figures are) as well as to the "VISITS" table and then I created a relationship using a 3rd table called "FY" with the unique fiscal year values.

 

 I would appreciate any inputs, ideas, suggestions you may have to make this work 🙂

 

THANKS A LOT IN ADVANCE!

 

Best regards,

 

Borja

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

@bortxs,

Create the following measures in your table and check if they returns your expected result.

Previous Fiscal Year to Date  Net order Value = CALCULATE ( [Fiscal Year To Date Net order Value]; DATEADD(DATE[Date];-1;YEAR) )

 

Fiscal Year To Date Ordernumber = TOTALYTD(DISTINCTCOUNT('DWH'[Ordernumber]);DATE[Date];"31.09")
Fiscal Year To Date Sessions = TOTALYTD(SUM(VISITS[Sessions]);DATE[Date];"31.09")

 

Previous Fiscal Year To Date Conversion Rate = CALCULATE([Fiscal Year To Date Ordernumber];SAMEPERIODLASTYEAR(DATE[Date])) /CALCULATE([Fiscal Year To Date Sessions];SAMEPERIODLASTYEAR(DATE[Date]))


If the above formulas don't help, please share sample data of your tables or PBIX file for us to analyze.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft

 

Thanks a lot for your answer and SORRY FOR MY LATE REPLY! (some holidays are -among others- blamable for that... Smiley Happy)

 

I´ve tried your solution and created those measures but unfortunately I get nothing... Smiley Sad but thanks for your effort and support anyway.

 

It´s a quite big file (~480 Mb) and I may need to start over again from the scratch and with only one country maybe (I have ~10) so that nothing gets mixed up and I can get the report up and properly running?

 

Regards,

 

Borja

@bortxs,

I am not sure if you can get expected result without your sample data. You can upload the PBIX file to OneDrive and send me the shared link via Private Message.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia, 

 

thanks for your quick reply and help! Smiley Happy

I´ve seen that apparently our company has ondrive blocked... I´ll try to contact our IT and see if I can "jump" this firewall in order to be able to send you the file, ok?

Regards,

 

Borja

@bortxs,

Ok, you can upload the file to other online tools as long as I can download the file.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.