Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Creating a Custom Power BI Line Chart for Year-to-Date Sales with Conditional Month Filtering

Hi all 

I'm working with a measure called 'SalesYTD' and want to display it in a line chart with the months on the X-axis. Here's how I'd like it to behave:

  • For Years Before the Current Year (Date[Year] < This Year): Display data for all months.
  • For Years After the Current Year (Date[Year] > This Year): Leave these as blank.
  • For the Current Year (Date[Year] = This Year): The display depends on the selected month:
    • If a Future Month is Selected (Date[Month] > Current Month): Show data up to the previous month only. The current and future months should be blank.
    • If the Current or an Earlier Month is Selected (Date[Month] <= Current Month): Display data only up to the month before the selected one. For instance, if September is chosen, data should be shown only until August.

However, I'm encountering an issue. When I select a month, the chart only displays data for that month instead of cumulatively up to that month.

I've considered using variables and the DATESBETWEEN function, but it's leading to errors. Note that 'SalesYTD' is built on a few other measures and utilizes the TOTALYTD function.

Could you assist me in resolving this? Perhaps with some code examples, I might be able to pinpoint what I'm missing.

Thanks in advance!

Need Help
Tips and Tricks
Tutorial Requests

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello Anonymous,

     

    Can you please try this:

     

    1. Define Helper Measures/Variables

    Current Year = YEAR(TODAY())
Current Month = MONTH(TODAY())
Selected Year = MAX(Date[Year])
Selected Month = MAX(Date[Month])

    2. Modify the SalesYTD Measure

    SalesYTD Conditional = 
VAR CurrentYear = [Current Year]
VAR CurrentMonth = [Current Month]
VAR SelectedYear = [Selected Year]
VAR SelectedMonth = [Selected Month]
VAR StartDate = DATE(CurrentYear, 1, 1)
VAR EndDate = 
    IF(
        SelectedYear < CurrentYear,
        DATE(SelectedYear, 12, 31),
        IF(
            SelectedYear > CurrentYear,
            BLANK(),  // Future years
            IF(
                SelectedMonth > CurrentMonth,
                DATE(CurrentYear, CurrentMonth, 1) - 1,
                DATE(CurrentYear, SelectedMonth, 1) - 1
            )
        )
    )
RETURN
IF(
    ISBLANK(EndDate),
    BLANK(),
    CALCULATE(
        [SalesYTD],
        DATESBETWEEN(Date[Date], StartDate, EndDate)
    )
)

     

    Should you require any further assistance, please do not hesitate to reach out to me.