How to DAX dynamic last month following from date filter

[lolingene]

Hi There,

I tried many solution that I can find on desktop reserch, but still unsuccessful to create DAX to get the number of last month, which are dynamic and follow from date filter. The details here.

Background:

I have a data table follow the picture below.

Result:

Would like to create a measure to show the number of last month total sales, can be relate with Date.

If Date filter is selected at before 9 May 2024, result should return:

• This month = the number in blue box = May 2024 = 728
• Last month = the number in red box = April 2024 = 4,684

But, if Date filter is selected at before 30 April 2024, result should return:

• This month = the number in blue box = April 2024 = 4,684
• Last month = the number in red box = March 2024 = 4,838

Question:

How to write a DAX to create a measure to get a number of "Last month total sales" (in the red box) and "This month total sales" (in the blue box)?

Recheck result:

Total sales of each month following picture below.

Sincerely,

Lolin Gene

[lolingene]

@v-jtian-msft@johnbasha33@amitchandak

I also have another question request to create measure following from detail below.
Background:

• I have 2 datasource, which are 'calendar' and 'daily_sales'. 
  • 'calendar' is a temp table data of date from 1Apr, 2024 - 31 July, 2024.
  • 'daily_sales' is data transaction data follow from picture below. 
  • 
  • I also create a measure in source 'daily_sales' to get a last month total sales
    [total_sales_last_month] =
    

    VAR SelectedDate = MAX('Calendar'[Date])

    VAR LastMonthStart = EOMONTH(SelectedDate, -2) +1

    VAR LastMonthEnd = EOMONTH(SelectedDate, -1)

    
    

    RETURN

      CALCULATE(

        ('daily_sales'[total_sales]),

        FILTER (

         'Calendar',

         'Calendar'[Date] >= LastMonthStart &&

         'Calendar'[Date] <= LastMonthEnd

        )

      )

 Question:

How can I wirte dax to create a measure to calculate MTD last month of sales, in the case it should be count only total_sales target from 1-2 June 2024 even in dashboard it has date filter before 31 July 2024?


Expectation result: a measure to show only MTD last month total sales number

Best regards,

Gene

[v-jtian-msft]

@lolingene 
Here is the test pbix.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[v-jtian-msft · Accepted solution]

Hi,@lolingeneHello, @johnbasha33and @amitchandak ,thanks for your concern about this issue. 
Your solutions are great!
And I would like to share some additional solutions below.

Based on the fact that you used the date filter, I judged that the data type of the Date column you used was a date type and not a text type, so I did the following test according to the date type data type

Here's the test data I created(I used two ways of writing and they both worked fine)

This month total sales = 
VAR selectMonth=MONTH(MAX('All_Sales'[Date]))
VAR selectYear =YEAR(MAX(All_Sales[Date]))

RETURN
  CALCULATE(SUM(All_Sales[Total sales]),FILTER(ALL('All_Sales'),MONTH('All_Sales'[Date])=selectMonth && 
YEAR('All_Sales'[Date])=selectYear))

Last month  total sales = 
VAR SelectDate =MAX('All_Sales'[Date])
VAR LastMonthEnd=EOMONTH(MAX('All_Sales'[Date]),-1)
VAR LastMonthStart=EOMONTH(MAX('All_Sales'[Date]),-2)+1
RETURN
CALCULATE(SUM(All_Sales[Total sales]),FILTER(ALL('All_Sales'),'All_Sales'[Date]>=LastMonthStart&&'All_Sales'[Date]<=LastMonthEnd))

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[johnbasha33]

@lolingene 

try the below
Last Month Sales =
VAR MaxDate = MAX('YourTable'[Date])
VAR LastMonthStartDate = DATEADD(MAX('YourTable'[Date]), -1, MONTH)
VAR LastMonthEndDate = EOMONTH(LastMonthStartDate)
RETURN
CALCULATE(
SUM('YourTable'[Sales]),
'YourTable'[Date] >= LastMonthStartDate && 'YourTable'[Date] <= LastMonthEndDate
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[lolingene]

Hi @johnbasha33 PREVIEW Thank you for your suggestion, I have tried your solution but found the error like below. Do you have any recommend to fix this?

Error: Too few arguments were passed to the EOMONTH function. The minimum argument count for the function is 2.

Regards,

Lolin Gene

[amitchandak]

@lolingene , for data you can have measures like

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

for name , try measure like

This Month Name =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)

return

format(_max, "MMM-YYYY")

Last Month Name =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)

return

format(_max, "MMM-YYYY")

[lolingene]

Hi amitchandak,

Thank you for your response, but seem like your DAX can't work with 'date' filter. If I'm filtered 'date' before 31 Mar 2024 The last month number will show blank.

So do you have any suggesttion to fix this issue, to show the last month number even date filter changed.

[johnbasha33]

@lolingene you can edit the interaction and turn it off for the visual, so when you select date last month will still displa