Forum Discussion
Sum data based on date range
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.
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
Please find the attachment for the data I am trying to get the results. It is just summing uo the entire data snd showing me for both years 2016 and 2017. sheet1 is the data I am working on while sheet 2 is to verify if the result on graph are correct or not.Dummy data
Hi siddhantk989,
I could not access the file data through that link, could you please check it again?:smileyvery-happy:
Hi tringuyenminh92,
Sorry for that. Please try accessingthe below link:
https://www.dropbox.com/s/dlddwq83h4t2dy0/Dummy%20Data.xlsx?dl=0
Hi Baskar,
As my guessing, I missed one step in my guideline that create relationship between Dates and Fact table:
tringuyenminh92 I have created relationship between the 2 tables but i dont know wh I am not getting the correct result for my data. Can you please try with the data that I have provided to you once
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.