topic Re: undefined in DAX Commands and Tips

undefined

Anonymous — Mon, 13 May 2024 23:11:32 GMT

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

Re: undefined

aduguid — Tue, 14 May 2024 05:35:03 GMT

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

Re: undefined

Anonymous — Tue, 14 May 2024 10:42:16 GMT

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 :

Re: undefined

Anonymous — Wed, 15 May 2024 08:33:24 GMT

Hi Anonymous

Thanks for the reply from aduguid.

Anonymous , 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.