Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Line Graph With Multiple Years of Data

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 No...
Need Help
  • adambhappy's avatar
    adambhappy
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
adambhappy
Resolver II
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      adambhappy
      Resolver II
      6 years ago

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