topic Re: Same Day Last Month in DAX Commands and Tips

Same Day Last Month

Anonymous — Mon, 09 Oct 2023 06:59:28 GMT

Hi,

Today is 9th Oct 2023 but in measure i want to show data of 9th Sep 2023 and i want it using time intelligence functions please help.

Re: Same Day Last Month

123abc — Mon, 09 Oct 2023 06:15:52 GMT

To display data from the same day last month in Power BI or a similar data analysis tool, you can use time intelligence functions. In this case, you want to show data for October 9th, 2023, which is one month before the current date of September 9th, 2023. You can achieve this by subtracting one month from the current date.

Here's how you can use DAX (Data Analysis Expressions) in Power BI to create a measure that shows data for the same day last month:

SameDayLastMonth =
CALCULATE(
[YourMeasure], -- Replace [YourMeasure] with the actual measure you want to display
DATEADD(CalendarTable[Date], -1, MONTH)
)

In this formula:

[YourMeasure] should be replaced with the measure you want to display for the same day last month.
CalendarTable[Date] should be replaced with the name of your date column in the calendar table.
DATEADD(CalendarTable[Date], -1, MONTH) subtracts one month from the current date in the specified date column.

You should have a calendar table that includes a date column that is connected to your data model. Make sure the relationships between your calendar table and other tables are properly set up for this to work.

Once you create this measure, you can use it in your visuals to display data for October 9th, 2023, even when the current date is September 9th, 2023.

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

Re: Same Day Last Month

Anonymous — Mon, 09 Oct 2023 06:58:15 GMT

Thanks for reply.

It is showing data from 1st Oct to 9th Oct but i want to show only 9th Sep 2023

Re: Same Day Last Month

Anonymous — Mon, 09 Oct 2023 07:00:37 GMT

Thanks for reply.

It is showing 1st Sep 2023 to 9th Sep 2023 data but i want to show only 9th Sep 2023 data

Re: Same Day Last Month

Anonymous — Mon, 09 Oct 2023 07:00:49 GMT

Thanks for reply.

It is showing 1st Sep 2023 to 9th Sep 2023 data but i want to show only 9th Sep 2023 data

Re: Same Day Last Month

Dangar332 — Mon, 09 Oct 2023 07:11:40 GMT

hi, Anonymous
try below

CALCULATE(
maxx(filter('calender table',CalendarTable[Date]'),[YourMeasure]),
DATEADD(CalendarTable[Date], -1, MONTH)
)

Re: Same Day Last Month

123abc — Mon, 09 Oct 2023 07:51:03 GMT

plz use below DAX:

SameDayLastMonth =
CALCULATE(
SUM('YourTable'[YourMeasure]),
FILTER('YourDateTable', 'YourDateTable'[DateColumn] = DATE(2023, 9, 9))
)

In this modified formula:

FILTER('YourDateTable', 'YourDateTable'[DateColumn] = DATE(2023, 9, 9)) filters the 'YourDateTable' to only include the rows where the date matches September 9, 2023.

This way, the SameDayLastMonth measure will specifically show data for the 9th of September 2023, as you requested.

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

Re: Same Day Last Month

Anonymous — Mon, 09 Oct 2023 10:43:55 GMT

Hi Anonymous ,

Based on your description, I created the DAX expression, you may check the result below:

New table: Table 2 = CALENDAR ( DATE ( 2023, 1, 1 ), TODAY () ) Measure: Same day = VAR _now = MAX ( 'Table 2'[Date] ) RETURN IF ( MONTH ( _now ) <> 1, DATE ( YEAR ( _now ), MONTH ( _now ) - 1, DAY ( _now ) ), DATE ( YEAR ( _now ) - 1, MONTH ( _now ) - 1 + 12, DAY ( _now ) ) )

An attachment for your reference. Hope it helps.

Best regards,

Community Support Team_ Scott Chang

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

Re: Same Day Last Month

Anonymous — Mon, 09 Oct 2023 11:21:09 GMT

Thanks for reply.

This will work for disconnected tables but in my model, dim_date table connected to Fact table. In this case, your code is not working

Re: Same Day Last Month

Anonymous — Wed, 11 Oct 2023 06:57:19 GMT

Hi Anonymous ,

Based on your statement, I make a new expression You can check the result as follows:

Measure:

Last month same day = var _pre= EOMONTH(MAX('Table'[DATE]),-1) return DATE(YEAR(_pre),MONTH(_pre),DAY(MAX('Table'[DATE])))

Perhaps this will work, and if you have any questions, please provide me a simple sample to make sure we can better solve the problem for you!

An attachment for your reference. Hope it helps.

Best regards,

Community Support Team_ Scott Chang

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