Dynamically calculate 'last month sales amount' when filtering in a table

[it_kwwk]

Hi there, 

I want to show two columns in the same table: one for the sales amount of the last month and one for the sales amount of the current month.

Using DAX, I've already calculated the 'Current Month Sales Amount'. The data shown in the table can dynamically change based on the year and month I select. And the results are correct.

The issue is that the 'Last Month Sales Amount' doesn't work when I use slicers (it appears empty or shows all results as 0). The 'Last Month Sales Amount' only works correctly when no slicers are applied (in other words, in the default case).

The screenshot shows below:

Here is the DAX of LastMonth sales:

LastMonth_Sales = 
VAR SelectedYear = COALESCE(SELECTEDVALUE('Append2_for line chart and table'[DocYear]), YEAR(TODAY()))
VAR SelectedMonth = COALESCE(
    SWITCH(
        SELECTEDVALUE('Append2_for line chart and table'[DocMonth]),
        "January", 1, "February", 2, "March", 3, "April", 4,
        "May", 5, "June", 6, "July", 7, "August", 8,
        "September", 9, "October", 10, "November", 11, "December", 12,
        BLANK()
    ),
    MONTH(TODAY())
)
VAR LastMonth = MOD(SelectedMonth - 2, 12) + 1  // Efficient calculation for last month
VAR LastMonthYear = SelectedYear - IF(LastMonth > SelectedMonth, 1, 0)  // Adjust year if crossing January
VAR StartDateLast = DATE(LastMonthYear, LastMonth, 1)
VAR EndDateLast = EOMONTH(StartDateLast, 0)

RETURN
CALCULATE(
    [Filtered_Sales_Amount],
    DATESBETWEEN(
        'Append2_for line chart and table'[DocDate],
        StartDateLast,
        EndDateLast
    )
)

Here is the DAX of CurrentMonth Sales:

CurrentMonth_Sales = 
VAR SelectedYear = COALESCE(SELECTEDVALUE('Append2_for line chart and table'[DocYear]), YEAR(TODAY()))
VAR SelectedMonth = COALESCE(
    SWITCH(
        SELECTEDVALUE('Append2_for line chart and table'[DocMonth]),
        "January", 1, "February", 2, "March", 3, "April", 4,
        "May", 5, "June", 6, "July", 7, "August", 8,
        "September", 9, "October", 10, "November", 11, "December", 12,
        BLANK()
    ),
    MONTH(TODAY())
)
VAR StartDate = DATE(SelectedYear, SelectedMonth, 1)
VAR EndDate = EOMONTH(StartDate, 0)

RETURN
CALCULATE(
    [Filtered_Sales_Amount],
    DATESBETWEEN(
        'Append2_for line chart and table'[DocDate],
        StartDate,
        EndDate
    )
)

 Can anyone solve this? Thank you!

[Ray_Minds]

Hi @it_kwwk 

1. Need to create a Date table  based on the Min and Max column of the Sales table as i am using finance table .
   Dax to create Table :- Date Dimension = GENERATESERIES ( MIN ( financials[Date] ), MAX ( financials[Date]),1)
   create year column :- Year = YEAR('Date Dimension '[Date])
   Month = MONTH('Date Dimension'[Date])
   Month Name = FORMAT('Date Dimension'[Date],"MMMM")
2. Create relationship between these table based on the Date column and ensure that column formate should be same for both 

3. Create current Month sales Measure 

Current Month Sales =

CALCULATE(

    SUM(financials[ Sales]),

    DATEADD('Date Dimension'[Date], 0, MONTH)

)

4. Create Last Month Sales Measure 

Last Month Sales =

CALCULATE(

    SUM(financials[ Sales]),

    DATEADD('Date Dimension'[Date], -1, MONTH)

)

5. Add into the visualization for last and current month and add the slicer 

6. Validate the current Month sales based on the selected slicer = feb

7. we showing the last month Data as well for the selected slicer 

Note :- We are using the validated table to bypass the slicer filter for data validation prospective.

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

[rajendraongole1 · Accepted solution]

Hi @it_kwwk  - you need to adjust the context in the CALCULATE function so it ignores or manipulates the slicer filters appropriately.

Update your last month sales measure as below:

LastMonth_Sales =
VAR SelectedYear = COALESCE(SELECTEDVALUE('Append2_for line chart and table'[DocYear]), YEAR(TODAY()))
VAR SelectedMonth = COALESCE(
SWITCH(
SELECTEDVALUE('Append2_for line chart and table'[DocMonth]),
"January", 1, "February", 2, "March", 3, "April", 4,
"May", 5, "June", 6, "July", 7, "August", 8,
"September", 9, "October", 10, "November", 11, "December", 12,
BLANK()
),
MONTH(TODAY())
)
VAR LastMonth = MOD(SelectedMonth - 2, 12) + 1 // Efficient calculation for last month
VAR LastMonthYear = SelectedYear - IF(LastMonth > SelectedMonth, 1, 0) // Adjust year if crossing January
VAR StartDateLast = DATE(LastMonthYear, LastMonth, 1)
VAR EndDateLast = EOMONTH(StartDateLast, 0)

RETURN
CALCULATE(
[Filtered_Sales_Amount],
DATESBETWEEN(
'Append2_for line chart and table'[DocDate],
StartDateLast,
EndDateLast
),
REMOVEFILTERS('Append2_for line chart and table'[DocYear], 'Append2_for line chart and table'[DocMonth]) // Remove slicer context on year and month
)

Verify that the LastMonth_Sales column displays the correct values for the previous month, even when slicers are used.I hope this works.

[it_kwwk]

Thank you so much! It works, really appreciate it!