Time Intelligence - Trading Day STD

[Anonymous]

Good morning All.

I am trying to develop a measure that calculates say purchases to date for the same period last year. The only challenge is that my data (because of our agriculture industry) is not based on conventional days of the month, but rather on "trading days". This is due to the fact that the season can begin on different days between 2 years.

For example I will need to know how much cotton we have purchased by trading day number 6 of this year compared to how much cotton we had purchased on the same trading day in the previous year.

Day 6 this year could be on 18 April 2018, whilst day 6 last year was on 12 April 2017. The different dates are not so important, I just need a way to compare between trading days and not use conventional days.

I already have a function which converts dates into trading day numbers, so the trading day numbers for both years are already inbuilt into my facttable.

Hope this makes sense.

Any ideas most welcome.

Thanks

Herbz

[v-yulgu-msft]

Hi @Anonymous,

Could you please share some sample data so that I can better understand your requirement and test for you.

"Day 6 this year could be on 18 April 2018, whilst day 6 last year was on 12 April 2017." How do we know day6 in last year is on 12 April 2017? What is the mapping relationship between the same day in this year and last year? 

"I already have a function which converts dates into trading day numbers, so the trading day numbers for both years are already inbuilt into my facttable." How did you convert dates to trading day numbers? Please post some sample data in facttable.

Regards,

Yuliana Gu

[Anonymous]

Hi Yuliana Gu

Thank you for your response.

I have created and attached some dummy data to illustrate the challenge.

https://www.dropbox.com/s/y47316tmtlmzbet/Cotton%20Purchases.xlsx?dl=0

The scenario is that we buy cotton from farmers every season. The assumption here is that the 2017 data represents all the purchases we made in 2017. Assume that 2018 is a partial year in that we are only on trading day number 78 if you look at the data.

What I am trying to do is compare the amount of cotton I have purchased to date in 2018 as compared to the same period in 2017. The comparison is based on the trading day and NOT the date as the purchasing season can start on very different dates from year to year. Simply put a measure to calculate how much cotton we have purchased to date in 2018 (based on max trading day which is 78) and then how much cotton had we bought by day 78 in 2017.

I need to be able to use this measures as a cumulative line graph showing the progressive increase in the cotton purchases and be able to filter by Area or province.

Hope this makes sense.

Thank you

Herbz