HOW to show last 6 months sales in bar chart when user select Month

[suprdata]

HOW to show last 6 months sales in bar chart when user select Month

[DataNinja777 · Accepted solution]

Hi @suprdata ,

To show the last six months of sales in a bar chart when a user selects a month, you need to create a DAX measure that dynamically filters sales data based on the selected month. The following DAX measure calculates sales for the last six months relative to the selected date:

Last6MonthsSales = 
VAR SelectedMonth = MAX('DateTable'[Date])
RETURN
CALCULATE(
    SUM(SalesTable[SalesAmount]),
    DATESINPERIOD(
        'DateTable'[Date], 
        SelectedMonth, 
        -6, 
        MONTH
    )
)

Once the measure is created, add a bar chart in Power BI and use the 'DateTable'[MonthYear] column as the X-axis and the Last6MonthsSales measure as the Y-axis. Ensure that the DateTable is properly related to SalesTable via the date column. When a user selects a specific month, the measure will dynamically adjust to show data for the last six months, including the selected month.
An alternative method is to apply a relative date filter directly in the bar chart's filters pane. Select the chart, add a filter on 'DateTable'[Date], choose relative date filtering, and set it to show the last six months. This ensures that the visualization always reflects the last six months dynamically without requiring a DAX measure.

Best regards,

[Bibiano_Geraldo]

Hi @suprdata ,

Considering that you have sales table, to achieve your goal, first you need to create a disconnected calendar table, something like this:

Calendar = 
CALENDAR(
    MIN(financials[Date]),
    MAX(financials[Date])
)

 after created the table, now you can create these two calculated columns in the calendar tble:

Month Name = FORMAT('Calendar'[Date], "mmm-yy")

Month No = VALUE(FORMAT('Calendar'[Date], "yyyymm"))

At this point, your table should look like this:

Now select the month name column and sort by month no. column:

Make sure that these tables are NOT related:

Now create a measure to calculate last 6 months:

Last6MonthSales = 
VAR vLastDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
    SUM(Sales[ Sales]),
    DATESINPERIOD(Sales[Date],vLastDate,-6,MONTH)
)

In your report add a new slicer with month name column from disconnected calendar table, and add the column chart, in your x-axis add the date or month column from your sales table and in y-axis add the last6monthsales measure.

Your output should look like this:

[danextian · Accepted solution]

Hi @suprdata 

You will need to use a disconnected dates/period table as using one that has a relationship to your fact table will show only the rows that have been selected, and then create a measure that references a column from that table.

Here's a sample measure

Sales Disconnected - L6M = 
CALCULATE (
    [Sales],
    KEEPFILTERS (
        DATESINPERIOD ( Dates[Date], MAX ( DisconnectedDates[Date] ), -6, MONTH )
    )
)

 Please see the attached pbix for the details.

[DataNinja777 · Accepted solution]

Hi @suprdata ,

To show the last six months of sales in a bar chart when a user selects a month, you need to create a DAX measure that dynamically filters sales data based on the selected month. The following DAX measure calculates sales for the last six months relative to the selected date:

Last6MonthsSales = 
VAR SelectedMonth = MAX('DateTable'[Date])
RETURN
CALCULATE(
    SUM(SalesTable[SalesAmount]),
    DATESINPERIOD(
        'DateTable'[Date], 
        SelectedMonth, 
        -6, 
        MONTH
    )
)

Once the measure is created, add a bar chart in Power BI and use the 'DateTable'[MonthYear] column as the X-axis and the Last6MonthsSales measure as the Y-axis. Ensure that the DateTable is properly related to SalesTable via the date column. When a user selects a specific month, the measure will dynamically adjust to show data for the last six months, including the selected month.
An alternative method is to apply a relative date filter directly in the bar chart's filters pane. Select the chart, add a filter on 'DateTable'[Date], choose relative date filtering, and set it to show the last six months. This ensures that the visualization always reflects the last six months dynamically without requiring a DAX measure.

Best regards,