Previous months data generated for line chart

[Puja_Kumari25]

Hi  Team,

I am using the below-mentioned dax for year-wise revenue calculation. Here we have a date table  which is used in date slicer where we hide min date to look like the calendar visual.  Users can select a date from the calendar, if no date is selected, the current date will be the max date.

We have generated a line chart for the year-wise sales. We have dropped only months from the date hierarchy. 

if we select a date , the line chart shows only data till the selected data or current date but it generates data for the other months.

Please help me  our line chart will be a straight line for zero for the months that are after selected date. 

ForYearSales =

VAR SelectedDate = MAX('CalendarFY'[Date])  -- Get the selected date from the slicer

VAR StartfYear =

    IF(

        ISBLANK(SelectedDate),  -- Check if no date is selected

        DATE(YEAR(TODAY()), 1, 1),  -- If no date is selected, get the start of the current year (1st January)

        DATE(YEAR(SelectedDate), 1, 1)  -- If a date is selected, get the start of the selected year (1st January)

    )

VAR EndfYear =

    IF(

        ISBLANK(SelectedDate),  -- Check if no date is selected

        TODAY(),  -- If no date is selected, use today's date as the end of the year

        SelectedDate  -- If date is selected, use the selected date as the end of the year

    )

VAR SalesInRange =

    CALCULATE(

        [ActualSales],  -- Calculate the sales measure

        'CalendarFY'[Date] >= StartfYear && 'CalendarFY'[Date] <= EndfYear  -- Filter for the selected year or current year

    )

RETURN

    IF(ISBLANK(SalesInRange), 0, SalesInRange)  -- Return 0 if no sales data is found for the given period

[Anonymous · Accepted solution]

Hi @Puja_Kumari25  ,

You only need to modify the first variable in the measure.

VAR SelectedDate = CALCULATE(MAX('CalendarFY'[Date]),ALLSELECTED())

And the measure is as follows.

ForYearSales = 
VAR SelectedDate = CALCULATE(MAX('CalendarFY'[Date]),ALLSELECTED())  -- Get the selected date from the slicer
VAR StartfYear = 
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        DATE(YEAR(TODAY()), 1, 1),  -- If no date is selected, get the start of the current year (1st January)
        DATE(YEAR(SelectedDate), 1, 1)  -- If a date is selected, get the start of the selected year (1st January)
    )
VAR EndfYear = 
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        TODAY(),  -- If no date is selected, use today's date as the end of the year
        SelectedDate  -- If date is selected, use the selected date as the end of the year
    )


VAR SalesInRange = 
    CALCULATE(
        [ActualSales],  -- Calculate the sales measure
        'CalendarFY'[Date] >= StartfYear && 'CalendarFY'[Date] <= EndfYear   && 'CalendarFY'[year] >=  YEAR(StartfYear) && 'CalendarFY'[year] <=  YEAR(EndfYear)
    )
RETURN
    IF(ISBLANK(SalesInRange), 0, SalesInRange)  -- Return 0 if no sales data is found for the given period

The final result is as follows. The line chart will be a zero line for the month following the selected date.

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.

[Puja_Kumari25]

Thank you for help

[Poojara_D12]

Hi @Puja_Kumari25 

Can you please try this:

ForYearSales =
VAR SelectedDate = MAX('CalendarFY'[Date])  -- Get the selected date from the slicer
VAR StartfYear =
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        DATE(YEAR(TODAY()), 1, 1),  -- If no date is selected, get the start of the current year (1st January)
        DATE(YEAR(SelectedDate), 1, 1)  -- If a date is selected, get the start of the selected year (1st January)
    )
VAR EndfYear =
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        TODAY(),  -- If no date is selected, use today's date as the end of the year
        SelectedDate  -- If a date is selected, use the selected date as the end of the year
    )
VAR IsInRange =
    MAX('CalendarFY'[Date]) <= EndfYear  -- Check if the current date in the context is within the selected range
VAR SalesInRange =
    CALCULATE(
        [ActualSales],  -- Calculate the sales measure
        'CalendarFY'[Date] >= StartfYear && 'CalendarFY'[Date] <= EndfYear  -- Filter for the selected year or current year
    )
RETURN
    IF(IsInRange, SalesInRange, 0)  -- Show actual sales for dates in range, otherwise show 0

Power BI's visuals respect the context of the data being displayed. By explicitly setting the value to zero for months outside the range, the line chart will draw a straight line at zero for these months.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Puja_Kumari25]

We are dropping months on the axis. We need only a line chart for the selected date's year. here line chart is generating and data for previous all year's months

sample file

[Anonymous]

Hi @Puja_Kumari25 ,

I'm not sure how your dataset is designed, here is my sample.

Creates a disconnected calendar table with date fields derived from the dates of the data table.

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

The measure is as follows.

ForYearSales = 
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])  -- Get the selected date from the slicer
VAR StartfYear =
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        DATE(YEAR(TODAY()), 1, 1),  -- If no date is selected, get the start of the current year (1st January)
        DATE(YEAR(SelectedDate), 1, 1)  -- If a date is selected, get the start of the selected year (1st January)
    )
VAR EndfYear =
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        TODAY(),  -- If no date is selected, use today's date as the end of the year
        SelectedDate  -- If date is selected, use the selected date as the end of the year
    )
VAR SalesInRange =
    CALCULATE(
        [ActualSales],  -- Calculate the sales measure
       KEEPFILTERS('Table'[Date] >= StartfYear && 'Table'[Date] <= EndfYear)  -- Filter for the selected year or current year
    )

RETURN
    IF(ISBLANK(SelectedDate), --If no date is selected
    IF(
        MAX('Table'[Date])<StartfYear,BLANK(), -- Dates less than the current year are blank.
        IF(ISBLANK(SalesInRange),0,SalesInRange) -- Returns 0 if no sales data was found for the given period
    ), 
    IF(ISBLANK(SalesInRange),0,SalesInRange) -- Return 0 if no sales data is found for the given period
)

Results when a date is selected from the calendar.

Result of not selecting a calendar date.

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.