undefined

[Meriem]

Hi all,

please I'm trying to calculate a measure for the last month same period and display it on a matrix visual , where I'm having the sales per day .

i.e if today is 14.05.2024 , I want to calculate sum sales for the previous month ( from the 1st to 14 / 04 .2024 

Please any help !! 

[v-xuxinyi-msft · Accepted solution]

Hi @Meriem 

Thanks for the reply from @aduguid.

@Meriem , the following testing is for your reference:

My sample:

Create a measure as follows

Measure = 
VAR _lmonthfirstday = EOMONTH(TODAY(), -2) + 1
VAR _lmonth = EDATE(TODAY(), -1)
VAR _sum = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Date] >= _lmonthfirstday && [Date] <= _lmonth))
RETURN
_sum

Output:

Since I don't know what your data structure is like, I have only tested up to this point, so if the method has problems with your data, please provide some data so that I can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

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

[Meriem]

I tried it but it does not work dear ,

actually , i have created a calendar spanning from 2014 to 2024 and used it as a filter to segment the dates (linked it to my table).

and i have created three measures for this purpose. However, I'm encountering difficulties with the "LAST MONTH'S SALES (SAME PERIOD)" measure. 

my table is as below :

[aduguid]

You can use a DAX query for timeframes.

Calendar Timeframe = 
VAR _today_date =                   'Properties'[Today Date] // or TODAY() or DATE(2023, 07, 15)
VAR _month_start =                  DATE( YEAR(_today_date), MONTH(_today_date), 01 )
VAR _month_start_py =               DATE( YEAR(_today_date) - 1, MONTH(_today_date), 01 )
VAR _today_date_py =                DATE( YEAR(_today_date) - 1, MONTH(_today_date), DAY(_today_date) )

VAR _result = 
    UNION (
      ADDCOLUMNS (CALENDAR ( _month_start, _today_date ),       "Timeframe", "MTD",     "Timeframe Order", 1 )
    , ADDCOLUMNS (CALENDAR ( _month_start_py, _today_date_py ), "Timeframe", "MTD_PY",  "Timeframe Order", 2 )
    )

RETURN
_result

Then create the relationship to your calendar table

After that you can use one measure to calculate each timeframe. 

I have more examples of timeframes in my GitHub project here