How to sum the number of last month following from day of month of date filter

[Anonymous]

Hi There,

I have a issue to create calculation metric, follow from the senerio below.

Background:

If the date filter selected before 20 May 2024, the number of "Total sales last month" should be calculate from date 1-20 April 2024 only. 

I have date filter and table from below.

Result:

1.When select date filter before 5/13/2024

>> "Measure_MTD" of "Total sales last month" should be return 26

>> "Total sales last month" should be calculate from day 1-13 of April 2024

But, currently it shows blank value.

2.When select date filter before 4/30/2024

>> "Measure_MTD" of "Total sales last month" should be return 300

>> "Total sales last month" should be calculate from day 1-30 of Mar 2024

But, currently it shows blank value.

Measure calculation:

• Total sales MTD last month =

CALCULATE(

'daily_sales'[Total sales last month],

ALLEXCEPT('daily_sales', 'daily_sales'[date_month]),

DATESMTD('daily_sales'[date_month])

)

• Total sales last month =

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

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

VAR LastMonthEnd = EOMONTH(SelectedDate, -1)

RETURN

  CALCULATE(

    SUM('daily_sales'[total_sales_MUSD]),

    FILTER (

     'Calendar',

     'Calendar'[Date] >= LastMonthStart &&

     'Calendar'[Date] <= LastMonthEnd

    )

  )

Question:

How to create a measure?

Best regards,

lolingene

[fahadqadir3]

@Anonymous  Review the attached screenshot and power bi file: 

@Anonymous

Power Bi File:

MTD 1.pbix

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

[Anonymous]

@fahadqadir3  Thank you for your response, but i can't open your PowerBI file as I used the older version (Version: 2.117.984.0 64-bit (May 2023)). Could you please save file for older version?

[fahadqadir3 · Accepted solution]

Sales MTD = 

VAR _MaxDate =CALCULATE(MAX('Table'[Date]),REMOVEFILTERS('Calendar'))

VAR _Date = CALENDAR(DATE(YEAR(_MaxDate),MONTH(_MaxDate),1),_MaxDate)

RETURN

CALCULATE(SUM('Table'[Sales]),KEEPFILTERS('Calendar'[Date] IN _Date))

LastMMTD = 

VAR _MaxDate = CALCULATE(MAX('Table'[Date]),REMOVEFILTERS('Calendar'))

VAR _Date = SELECTCOLUMNS( CALENDAR(DATE(MONTH(_MaxDate),1,1),_MaxDate),"Date",EDATE([Date],-1))

RETURN

CALCULATE(SUM('Table'[Sales]), KEEPFILTERS('Calendar'[Date] in _Date))

@Anonymous No, I'm not able to save as previous version.

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

[Anonymous]

@fahadqadir3 
Can you show me as a model view. It can't works for my-side.

I tried to follow your code here.

Total sales MTD last month = 

VAR _MaxDate = CALCULATE(MAX('daily_sales'[date_month]), REMOVEFILTERS('Calendar'))

VAR _Date = SELECTCOLUMNS( CALENDAR(DATE(MONTH(_MaxDate),1,1),_MaxDate),"Date",EDATE([Date],-1))

RETURN
CALCULATE(('daily_sales'[Total sales last month]), KEEPFILTERS('Calendar'[Date] in _Date))

But it turn the wrong value here.

*Date month filter from 'Calendar' source.

I have a model follow from this picture below.

And also tried this code below.

Total sales MTD last month = 

VAR _MaxDate = CALCULATE(MAX('Calendar'[Date]), REMOVEFILTERS('Calendar'))
VAR _Date = SELECTCOLUMNS( CALENDAR(DATE(MONTH(_MaxDate),1,1),_MaxDate),"Date",EDATE([Date],-1))

RETURN
CALCULATE(('daily_sales'[Total sales last month]), KEEPFILTERS('daily_sales'[date_month] in _Date))

When I create a table the result of Total sales MTD last month is wrong, as it return result = 40.
Seem like my measure calculate 1-20 Apr 2024 as filter date month is before 13 May 2024

Do I did anything wrong?

[fahadqadir3]

@Anonymous Share your sample workbook.