topic Re: Previous months data generated for line chart in DAX Commands and Tips

Previous months data generated for line chart

Puja_Kumari25 — Thu, 02 Jan 2025 19:15:28 GMT

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

Re: Previous months data generated for line chart

Anonymous — Fri, 03 Jan 2025 03:12:53 GMT

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.

Re: Previous months data generated for line chart

Puja_Kumari25 — Fri, 03 Jan 2025 21:43:48 GMT

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

Re: Previous months data generated for line chart

Poojara_D12 — Sat, 04 Jan 2025 04:43:42 GMT

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

Re: Previous months data generated for line chart

Anonymous — Mon, 06 Jan 2025 05:20:17 GMT

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.

Re: Previous months data generated for line chart

Puja_Kumari25 — Mon, 06 Jan 2025 09:35:45 GMT

Thank you for help