Partial Previous year value for fiscal year YTD (file uploaded)

[akbar]

Hi,

PBIX and CSV file here.

https://1drv.ms/f/s!AtdW26HVhBpigQ82i7SU4_oQdi-g

I have some mock data to replicate the report I am building. I have two date columns, one is the date an event was booked, the other is a date the event will start. The start date is what the counts need to be based on, as such that is what the calendar table is linked to. However I believe Order date would need to be used in order to capture the bookings that were made by this time last year (02/07/2017).

 I work on an academic year (Sep 1st to Aug 31st). Sep-Nov 30 =Q1. Jun-Aug 31 = Q4. What I'm after is a dynamic measure which will give me the total number of bookings made, by this time last year, shown in quarter format.

My current previous year measure only gives me a total previous year value. the PY ttly column is the figure i am after. It is 2 less for Q4-2018 (which would represent the number of bookings made for Q4-2017, at this current date last year, 02/07/2017). There were 2 bookings for Q4-2017 made after 02/07/2017, as such they shouldn't be included in the figure.

I would also like a column giving me the YTD of PY ttly.

Are these such measures possible to create? 

Thank you,

Akbar

[v-danhe-msft]

Hi @akbar,

From your description, I guess you would like to calculate “PY ttly” and “YTD of PY ttly”, right?

After looking into the shared pbix and csv files, I’m not very clear about the logic to calculate these two values. Would you please clarify them based on corresponding mock data?

Regards,

Daniel He

[akbar]

This message got deleted for some reason. Please do not delete it again, it is a genuine, non spam response, thanks.

hi @v-danhe-msft 

Thank you for taking the time to read and reply.

I have data stretching back a couple of years. The report has two seperate date fields. One is when the booking was made, the other is when the event is to start. The relationship between the date table and fact table is based on start date as that is what the report is to show, bookings for each quarter. The date format is;

1st Sep - Nov 30th = Q1

1st Dec - Feb 28th/29th = Q2

1st Mar - May 31st = Q3

1st Jun - Aug 31st = Q4

Bookings are made before the start date of a given event, and can be made well in advance, e.g. booking made in Q1 for event that is in Q4.

What I'm after is a dynamic measure which will give me the number of bookings made for the previous year, which have been made 1 year prior to the current date.

So if my data is showing the total number of bookings for each quarter of the current fiscal year, so Q1-2018, Q2-2018, Q3-2018, Q4-2018, I would like a measure which calculates how many bookings were made the year prior (Q1-2017, Q2-2017, Q3-2017, Q4-2017), but only the number of bookings that were made by this time last year (so in this case 03/07.2017). 

As the fiscal year for 2017 has already finished, my previous year measure shows the full previous years bookings for each quarter (all bookings made for the quarters Q1-Q4 fy 2017). However as the report wants to compare based on how we are performing this year compared to last year, I require the number of bookings that were made by this time last year (03/07.2017).

So although Q1-Q3 2017 would be complete by this time last year, Q4 wouldnt have. There would still be bookings made for Q4-2017 made after this date last year, bookings that were made after 03/07.2017 up to Aug 31st for Q4-2017. These such bookings need to be excluded from the sum as they hadnt been made yet by this time last year.

However my Previous year measure shows the full number of bookings for Q4-2017, which means the report would be showing Q4-2018 bookings made up to current date (03/07/2018), against all Q4-2017 bookings made last year (as all bookings for fy2017 had been made by 03/07/2018. I would want only the Q1-Q4 2017 bookings made by 03/07/2017, as the ones made from 04/07/2017 to 31/08/2017 (end of Q4), had not been made yet by this time last year.

Sorry for the long winded post, hope that was clear enough.

Thanks,

Akbar

[akbar]

hi @v-danhe-msft 

Thank you for taking the time to read and reply.

I have data stretching back a couple of years. The report has two seperate date fields. One is when the booking was made, the other is when the event is to start. The relationship between the date table and fact table is based on start date as that is what the report is to show, bookings for each quarter. The date format is;

1st Sep - Nov 30th = Q1

1st Dec - Feb 28th/29th = Q2

1st Mar - May 31st = Q3

1st Jun - Aug 31st = Q4

Bookings are made before the start date of a given event, and can be made well in advance, e.g. booking made in Q1 for event that is in Q4.

What I'm after is a dynamic measure which will give me the number of bookings made for the previous year, which have been made 1 year prior to the current date.

So if my data is showing the total number of bookings for each quarter of the current fiscal year, so Q1-2018, Q2-2018, Q3-2018, Q4-2018, I would like a measure which calculates how many bookings were made the year prior (Q1-2017, Q2-2017, Q3-2017, Q4-2017), but only the number of bookings that were made by this time last year (so in this case 03/07.2017). 

As the fiscal year for 2017 has already finished, my previous year measure shows the full previous years bookings for each quarter (all bookings made for the quarters Q1-Q4 fy 2017). However as the report wants to compare based on how we are performing this year compared to last year, I require the number of bookings that were made by this time last year (03/07.2017).

So although Q1-Q3 2017 would be complete by this time last year, Q4 wouldnt have. There would still be bookings made for Q4-2017 made after this date last year, bookings that were made after 03/07.2017 up to Aug 31st for Q4-2017. These such bookings need to be excluded from the sum as they hadnt been made yet by this time last year.

However my Previous year measure shows the full number of bookings for Q4-2017, which means the report would be showing Q4-2018 bookings made up to current date (03/07/2018), against all Q4-2017 bookings made last year (as all bookings for fy2017 had been made by 03/07/2018. I would want only the Q1-Q4 2017 bookings made by 03/07/2017, as the ones made from 04/07/2017 to 31/08/2017 (end of Q4), had not been made yet by this time last year.

Sorry for the long winded post, hope that was clear enough.

Thanks,

Akbar