cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Resolver IV
`Sales MTD = VAR _MaxDate =CALCULATE(MAX('Table'[Date]),REMOVEFILTERS('Calendar'))VAR _Date = CALENDAR(DATE(YEAR(_MaxDate),MONTH(_MaxDate),1),_MaxDate)RETURNCALCULATE(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))RETURNCALCULATE(SUM('Table'[Sales]), KEEPFILTERS('Calendar'[Date] in _Date))`

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

If I helped you, click on the Thumbs Up to give Kudos.

5 REPLIES 5
Resolver IV

@lolingene  Review the attached screenshot and power bi file:

Power Bi File:

MTD 1.pbix

If I helped you, click on the Thumbs Up to give Kudos.

Frequent Visitor

@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?

Resolver IV
`Sales MTD = VAR _MaxDate =CALCULATE(MAX('Table'[Date]),REMOVEFILTERS('Calendar'))VAR _Date = CALENDAR(DATE(YEAR(_MaxDate),MONTH(_MaxDate),1),_MaxDate)RETURNCALCULATE(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))RETURNCALCULATE(SUM('Table'[Sales]), KEEPFILTERS('Calendar'[Date] in _Date))`

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

If I helped you, click on the Thumbs Up to give Kudos.

Frequent Visitor

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

`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))RETURNCALCULATE(('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))RETURNCALCULATE(('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?

Resolver IV

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors