Forum Discussion

siddhantk989's avatar
siddhantk989
Helper III
9 years ago
Solved

Sum data based on date range

Hi,     I have a date column in my table and a sales column. The dummy data is as follows:   Date          Sales   10/1/2016    500 9/1/2016      200 8/1/2016      600 1/1/2017    100    No...
Need Help
Show and Tell
Tips and Tricks
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    Hi siddhantk989,

     

    It's same result, I just change connection and field name from Order Date-> Date to fit with your file with my column fields. 

     

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago

Hi siddhantk989,

 

I think your expectation is Time-pattern case, so please check my solution as below:

  • Create Dates table

 

Dates = CALENDARAUTO()
  •  Create relationship between Dates and Sales table:

 

 

  • Create calculated column in Dates table as Fiscal Date: (as my understand meaning of your fiscal year is Oct-Sep so i minus 3 months, you could adjust this number)
Fiscal Date = DATEADD(Dates[Date],-3,MONTH)

In sales table, create 2 calcualted measures for Cycle year and Fiscal Year:

Cycle Year = CALCULATE(SUM(Sales[Sales]),filter(all(Dates),sum(Sales[Sales])>0  && Dates[Date]<=MAX(Dates[Date]) && Dates[Date].[Year] = MAX(Dates[Date].[Year])    ))
Fiscal Year = CALCULATE(SUM(Sales[Sales]),filter(all(Dates),sum(Sales[Sales])>0  && Dates[Date] <= MAX(Dates[Fiscal Date]) 
	&& Dates[Date] >= MAX(Dates[Fiscal Date])-365 ))

 

Sample pbix File - Sample Data

 

Please check this approach with your data let me know if there is any mismatch. For more information, you could refer full topic

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

siddhantk989's avatar
siddhantk989
Helper III
9 years ago

Hi tringuyenminh92,

 

  Thanks for the solution but I dont know wh the above code for fiscal year is working for only 1 year, i.e., it is giving me results only for 2016 for rest all years 2015,2017 and so on I am not getting an data.  IS there any way to resolve this error?

Thanks in advance.

 

Regards,

Siddhant

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    Hi siddhantk989,

     

    I have tried cloned more data for 2015, Fiscal Year is working for serveral years. Could you please describe your expectation as picture or more information? so i could quickly adjust above solution.