Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • fahadqadir3's avatar
    fahadqadir3
    2 years ago
    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.

5 Replies

  • fahadqadir3's avatar
    fahadqadir3
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        fahadqadir3
        Icon for Solution Supplier rankSolution Supplier
        2 years ago
        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.