Dynamically calculating/marking varying festival period of 50 days for 3 years

[hemantsingh]

Hi fellas,

   I am trying to create a report wherein i need to show the sales performance week & day wise for last three years. I am required to create a bucket of 50 days for analysis. Problem is that these 50 days do not falls on same date in all years e.g for 2013, I need to create a bucket of 50 days starting from 1st sept 2013 till 20 oct 2913,for 2014 this bucket will start from 20 sept 2014 till 10 nov 2015 and so on for subsequent years. 

  I am unable to use sameperiodlastyear function else this analysis would have been a piece of cake. Can somebody help me in creating this buckets dynamically year wise as per requirement. Further there is one more thing which i do not want to do & that is to create a calculated column & mark corresponding transaction day in sales table as festival in that column & do that for all the years.

   Awaiting ideas & advices.

Regards,

[v-shex-msft]

Hi @hemantsingh,

You can follow below steps to achieve your requirement:

1. Add calculated column to check the date to add the state.

DateRange = if(MAX([Date])>=DATE(2013,9,1)&&MAX([Date])<=DATE(2013,10,20),"2013 sept 1 ~ 2013 oct 20",if(MAX([Date])>=DATE(2014,9,20)&&MAX([Date])<=DATE(2014,11,20),"2014 sept 20 ~ 2013 nov 20",BLANK()))

2. Add a measure to calculate different result based on above range:

Result = SWITCH([DateRange],"2013 sept 1 ~ 2013 oct 20",calculate method1,"2014 sept 20 ~ 2013 nov 20",calculate method2,BLANK())

3. Use above column and measure to create the chart.

Regards,

Xiaoxin Sheng

[hemantsingh]

Hi @v-shex-msft

  My sincere thanks for taking out time from your schedule for helping me. I tried your formula for date range. But it didn't work, reason is that the formula is trying to compare max date of date column with dates which are always lesser to it. Hence formula will always return blank.

  Any other ideas??

Regards,

Hemant