Line Graph With Multiple Years of Data

[Anonymous]

I am trying to graph material utilization for our factory as line graph. Each line will represent the utilization percentage by month for each year in the data. The month along the x-axis will run November through October of the following year. I've made a column to rearrange the months but I can not figure out of to differentiate by fiscal year. All of the data is in one master spread sheet. This is what I've tried so far. 

Fiscal Year = IF(DATESBETWEEN('Master Data'[End date].[Date],11/1/2017,10/31/2018),"Fiscal Year 2018",IF(DATESBETWEEN('Master Data'[End date].[Date],11/1/2018,10/31/2019),"Fiscal Year 2019"))

I get an error saying that duplicate dates are not supported. I can not delete these duplicates. The utilization data is by order number, and multiple orders are run per day. 

Thank you in advance for your help.

[adambhappy]

Since the end date is in a single column, why aren't you using a date table to give you the fiscal years?

[Anonymous]

Can you give me an example of this?

[adambhappy]

If you go into Modeling and select New Table you can enter the following code which will give you a calendar table from 2010 to 2030, I added the fiscal year column that gives you fiscal years that start with November based on the DAX code you gave in your initial post.

dates = 
VAR vMinYear = 2010
VAR vMaxYear = 2030
RETURN
ADDCOLUMNS (
        CALENDAR(DATE(vMinYear;1;1);DATE(vMaxYear;1;1));
    "Calendar Year"; "CY " & YEAR ( [Date] );
    "Calendar Year Number"; YEAR ( [Date] );
    "Fiscal Year"; "FY " & IF(MONTH([Date])<11;YEAR([Date])-1;Year([Date]));
    "Fiscal Year Number"; IF(MONTH([Date])<11;YEAR([Date])-1;Year([Date]));
    "Month Name"; FORMAT ( [Date]; "mmmm" );
    "Month Number"; MONTH ( [Date] );
    "Quarter"; "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)

Link the end date in your transaction table to the Date column in the newly created table and mark the table as a date table. Now you can use the date table to slice the data according to fiscal years.

[Anonymous]

That worked for creating the  fiscal years, but now I'm having issues with the order of the months. Also, the fiscal years are a year behind. It should be data for fiscal years 18 and 19.

[adambhappy · Accepted solution]

I've fixed the calculation for the Fiscal Year (reversed it the first time) and I've added a column called Fiscal Year Month Number, select the Month Name in the table and then change the Sort by Column to Fiscal Year Month Number.

dates = 
VAR vMinYear = 2010
VAR vMaxYear = 2030
RETURN
ADDCOLUMNS (
        CALENDAR(DATE(vMinYear;1;1);DATE(vMaxYear;1;1));
    "Calendar Year"; "CY " & YEAR ( [Date] );
    "Calendar Year Number"; YEAR ( [Date] );
    "Fiscal Year"; "FY " & IF(MONTH([Date])>10;YEAR([Date])+1;Year([Date]));
    "Fiscal Year Number"; IF(MONTH([Date])>10;YEAR([Date])+1;Year([Date]));
    "Fiscal Year Month Number";IF(MONTH([Date]<11);Month([Date])+10;Month([Date]));
    "Month Name"; FORMAT ( [Date]; "mmmm" );
    "Month Number"; MONTH ( [Date] )
)

Then you can change the visualization to sort by month name and make sure the sorting is descending.

[Anonymous]

@adambhappy  I was hoping you could help me make an adition to this date table. I am trying to add a column that will allow me to filter by week using a slicer with the date of the first day of the week. For example, this month the slicer would contain 11/04/19, 11/11/19,  11/18/19, and 11/25/19. When one of the dates is selected it would filter to show the data for that week.

[adambhappy]

@Anonymous Simply add a column where you remove the weekday from the [Date] column

dates = 
VAR vMinYear = 2010
VAR vMaxYear = 2030
RETURN
ADDCOLUMNS (
        CALENDAR(DATE(vMinYear;1;1);DATE(vMaxYear;1;1));
    "Calendar Year"; "CY " & YEAR ( [Date] );
    "Calendar Year Number"; YEAR ( [Date] );
    "Fiscal Year"; "FY " & IF(MONTH([Date])>10;YEAR([Date])+1;Year([Date]));
    "Fiscal Year Number"; IF(MONTH([Date])>10;YEAR([Date])+1;Year([Date]));
    "Fiscal Year Month Number";IF(MONTH([Date]<11);Month([Date])+10;Month([Date]));
    "Month Name"; FORMAT ( [Date]; "mmmm" );
    "Month Number"; MONTH ( [Date] );
    "Start of week"; [Date] - ( WEEKDAY([Date];2) - 1 )
)

[Anonymous]

Thank you so much for your help! That works perfectly!