Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
PPK2698
Helper II
Helper II

Same Day Last Month

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.

1 ACCEPTED SOLUTION

Hi @PPK2698 ,

 

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

vtianyichmsft_0-1697007198537.png

 

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.

View solution in original post

9 REPLIES 9
v-tianyich-msft
Community Support
Community Support

Hi @PPK2698 ,

 

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

vtianyichmsft_3-1696848091637.png

 

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.

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

Hi @PPK2698 ,

 

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

vtianyichmsft_0-1697007198537.png

 

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.

PPK2698
Helper II
Helper II

Thanks for reply.

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

123abc
Community Champion
Community Champion

plz use below DAX:

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

In this modified formula:

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

PPK2698
Helper II
Helper II

Thanks for reply.

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

123abc
Community Champion
Community Champion

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.

Thanks for reply.

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

hi, @PPK2698 
try below

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors