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 

 

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?

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. 

     

13 Replies

  • 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