Forum Discussion

skasu's avatar
skasu
Frequent Visitor
1 year ago
Solved

Line chart with YTD Years based on Slicer selection

Hi All,

 

Iam creating a Line chart with YTD years based on year of Slicer selection. If I select 2022 in slicer then the line chart should display YTD counts of 2022 and 2021 similar to below chart (but below chart is picking only current year and previous year not the years from slicer). 

 

 

I tried to create another table (YearsTable) but it is getting all the years starting from 2010 to 2025. The YearsTable should have only the selected year and Previous year.  This Year will be used in x axis of the line chart. Appreciate your response. Thanks.

 

YearsTable =
SUMMARIZE(
    'Date',
    'Date'[Date],
    "Year", 'Date'[Date]
)
 
 

 

Also tried creating new table but the  calculated table is not getting updated as they are static.

DateChartFilters =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR StartDate = DATE(SelectedYear - 1, 1, 1)
VAR EndDate = DATE(SelectedYear, 12, 31)
RETURN
    CALENDAR(StartDate,EndDate)
 

Kind Regards

SKasu

Need Help
  • skasu's avatar
    skasu
    1 year ago

    Thanks. This is fixed now. created a measure instead of a table for capturing the years.

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    skasu Create a dynamic table for the selected year and the previous year:

    YearsTable =
    VAR SelectedYear = SELECTEDVALUE('Date'[Year])
    RETURN
    FILTER(
    ADDCOLUMNS(
    CALENDAR(DATE(SelectedYear - 1, 1, 1), DATE(SelectedYear, 12, 31)),
    "Year", YEAR([Date])
    ),
    [Year] = SelectedYear || [Year] = SelectedYear - 1
    )

     

    Set the YearsTable as the data source for your line chart.
    Use the Year column from the YearsTable for the X-axis.
    Use your measure for YTD counts for the Y-axis.

     

    Create a measure for YTD counts:

    DAX
    YTDCount =
    CALCULATE(
    SUM('YourDataTable'[CountColumn]),
    DATESYTD('Date'[Date])
    )

     

    Add a slicer to your report and set it to filter by the Year column from your Date table.
    Ensure that the slicer is affecting the YearsTable and the line chart.

    • skasu's avatar
      skasu
      Frequent Visitor
      1 year ago

      Hi Bhanu, 

       

      Thanks for the response.

       

      Created the above YearsTable but showing blank when displaying the Year. Tried creating a measure for SELECTEDVALUE('Date'[Year]) in YearsTable and it was displaying the selected year.

       

       

       

       

       

      • skasu's avatar
        skasu
        Frequent Visitor
        1 year ago

        Thanks. This is fixed now. created a measure instead of a table for capturing the years.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Create a disconnected years table with all the years.

    YearsTable = 
DISTINCT(
    SELECTCOLUMNS(
        VALUES('Table'[Date]),
        "Year", YEAR([Date])
    )
)

    And create a slicer using the YearsTable[Year] column.

     

    Use the following measure as the filter for the line chart, set to is 1.

    Flag = 
VAR SelectedYear = SELECTEDVALUE(YearsTable[Year])
RETURN
IF( SelectedYear <> BLANK(),
    IF( 
        YEAR(SELECTEDVALUE('Table'[Date])) = SelectedYear || YEAR(SELECTEDVALUE('Table'[Date])) = SelectedYear -1,
        1, 0
    ),
    1
)

    This measure is used to help dynamically display the current and previous year's YTD based on slicer selection year.

     

    The final result is as follows. Hopefully it will meet your needs.

     

    Please see the attached pbix for reference.

     

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.