13 Replies Latest reply on Feb 8, 2019 5:53 AM by Adam Aliz

Any alternate way to calculate YTD Vs Prior YTD based on FY April

Hi,

From the screenshot, I have orderdate field , which consists  of 3 years  and month starting from January.

I want to calculate YTD for FY17/18 (Starting from April 2017 to the available date  )  and FY18/19 (Starting from April 2018 to.. the date available).

When the future date comes after refresh, it should calculate accordingly. Example : It should compare the current ytd FY 18/19, 01 April 2018 to 4 Feb 2019 with previous FY 17/18 , 01 April 2017 to 04 Feb 2018.

If future data available, it should compare with current YTD date with prior YTD date...

After calculating the Current YTD and previous YTD, i want to find the difference YTD YOY%.between current YTD vs Prior YTD.

Example, from the screenshot below, 72.4% is current YTD and 79.0% is previous YTD. When the future date comes both value should change according to the current date.

• 1. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Hi,

Hope this will help.

Mahfooj

• 2. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Hi mahfooj,

Andy using jan month to calculate ytd vs prior ytd. I need to calculate using month april..

• 3. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Below screenshot shows current yeart to date and previous year to date.you can use the filter to filter the data as well

Hope this helps

ZZ

Zhouyi Zhang

Hi Zhouyi,

Thanks for the reply. I have a small clarification , if you look at screenshot below for feb 2019 , date available from 01 Feb 2019 to 04 Feb 2019 . The total gives 17.

and comparing to feb 2018 , the same date from 01 Feb 2018 to 04 Feb 2018 . It should give 1868..

But in the chart, it shows for Feb 2018, total of 20301. Its taking the whole feb month for Feb 2018 and comparing with 4 days of Feb 2019.

Is it possible to compare the 01 /Feb/2018 to 04/Feb/2018 to 01/Feb/2019 only. so if data comes for tomorrow. it should compare with 01 to 06 Feb 2018 for current YTD with 01 to 06 Feb 2017 for previous YTD.

• 5. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

My initial solution is based on day, but I thought you are looking at month level.

Anyway, just need to update the calculation field of modified date as shown below to limit the data MTD.

ZZ

• 6. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Zhouyi Zhang

Hi Zhouyi,

Thanks for the solution.

• 7. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Hi Zhouyi, I would like to ask , whether its possible to show like this ?

The above bar chart shows 72.4% is current YTD and 79.0% is previous YTD. i want to show like this..

I am trying to use the reference field TO populate previous YTD. but i cant do it..

How to show current YTD and previous YTD like above graph using the below sample??

Attached is the workbook, fyi

• 8. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

something like this?

ZZ

• 9. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Zhouyi Zhang

Hi Zhouyi,,

Yes, exactly like this. thankyou. Since using LOD calc, it will automatically populate the data value right, whenever the new data comes in ?

• 10. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Try this one also. It works for sure.

Regards,

Prabhakar.

• 11. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Zhouyi Zhang

Hi Zhouyi,

I apologize ,  i used this same thread to ask doubt since its related ,I got one scenario, where by i need to show like this picture below again for another dashboard.

I applied your mentioned method above it works for one dashboard to calculate current YTD and previous YTD

But for another dashboard (Fields coming from different datasource), i used same technique . But when i applied the required field which is not a direct measure field but its a calculated one and when i tried to calculate Current YTD and Previous YTD both failed .

I have highlighted Field Sales , In my scenario, i am using calculated value. but it show like this second picture below

how to tackle this ? Your help is much appreciated

• 12. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

You need use lod to make sales calculation as non aggregate expression. Or is there any particular reason that you have to use aggregation (sum/avg) in the sales calculation?

ZZ

• 13. Re: Any alternate way to calculate YTD Vs Prior YTD based on FY April

Zhouyi Zhang

In my scenario, i derived one calculated value and it is connected with multiple calculation. Then final calculated value i am using as derived value in order to find the current YTD and previous YTD. As such, its failed and prompts as "Cannot mix agg and non-agg "..

In that case, to make it standard for all the dashboards instead of showing like this

it would be better to show like this or just a single KPI big number for current YTD only,