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. 

     

Baskar's avatar
Baskar
Resident Rockstar
9 years ago

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's avatar
    siddhantk989
    Helper III
    9 years ago

    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'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