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

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