Sum data based on date range

[siddhantk989]

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 

Now I want to show a graph displaying data for my 2 values. 1st is cycle year which is normal starting from jan to december. 2nd is fiscal year starting from october to september. I want to dispaly this data based on year on a bar graph. The graph should show 2 years 2016 and 2017. The Cycle Year sales for 2016 should be displayed as 1300 and 100 for 2017 while the fiscal year sales should be displayed as 800 for 2016 and 600 for 2017. I am able to display it for cycle year but I am having problem displaing data for fiscal  year. The  graph is not displaying 10/1/2016 data as 2017 data and thus I am not able to get the 600 value for 2017 on graph. I tried the datebetween function but it works for calculation part but when it comes to displaying data on graph the date 10/1/2016 is creating problem because of year.Is there any way to display the data for 10/1/2016 as 2017 data?

[Baskar]

Cool,

For this u have to create new calculated column.

Column =

var Cur_Month = Month(Date)

var Cur_Year = Year(Date)

return if ( Cur_Month > 9 , Cur_Year + 1 , Cur_Year )

So it will give u the new column with Fiscal year. Try this

Let me know if any help. 

[siddhantk989]

I was able to create the column but the problem is in displaying. Creating a new column gives me 2 date ranges now. So how do I display sales for both fiscal and cycle year on same graph now?

[tringuyenminh92]

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]

Hi @tringuyenminh92

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

[tringuyenminh92]

Hi @Baskar,

As my guessing, I missed one step in my guideline that create relationship between Dates and Fact table:

[siddhantk989]

@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

[tringuyenminh92 · Accepted solution]

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. 

[siddhantk989]

Hi @tringuyenminh92

I know the solution is perfectly fine and it is working with most of my code. but I just encountered 1 scenarion that If I have sales as 0 for Jan 2017 the fiscal year code for me is not working in this scenario. Please find the attched sample ata at the below link and kindly let me know where I am hoing wrong:

https://1drv.ms/x/s!AoXl4WEaulxtaUSydM8pD6I5vw0

Sorry for disturbing you again and agin but I am completely new to DAX coding.

Thanks in Advance.

Thanks,

Siddhant

[siddhantk989]

Hi @tringuyenminh92

Thanks a lot for your help. I think the problem was with the date format. The solution is working perfectly fine now.

[tringuyenminh92]

Hi @siddhantk989,

I could not access the file data through that link, could you please check it again?

[siddhantk989]

Hi @tringuyenminh92,

  Sorry for that. Please try accessingthe below link:

https://www.dropbox.com/s/dlddwq83h4t2dy0/Dummy%20Data.xlsx?dl=0

[siddhantk989]

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]

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.