Skip to main content
cancel
Showing results for 
Search instead 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

Reply
lolingene
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.

lolingene_0-1716265195456.png

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

lolingene_1-1716265260017.png

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

lolingene_2-1716265331285.png

lolingene_3-1716265341343.png

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

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))

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

ss.png

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.

View solution in original post

5 REPLIES 5
fahadqadir3
Solution Supplier
Solution Supplier

@lolingene  Review the attached screenshot and power bi file: 

@lolingeneMTD and LMTD.pngScreenshot_4.png

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.

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

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))

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

ss.png

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.

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

lolingene_3-1716287913816.png

*Date month filter from 'Calendar' source.


I have a model follow from this picture below.

lolingene_0-1716286513083.png

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

lolingene_2-1716287331750.png

 


Do I did anything wrong?

 

Screenshot_5.png

@lolingene Share your sample workbook.

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.