Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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.
Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for reply.

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

    • Dangar332's avatar
      Dangar332
      Icon for Resident Rockstar rankResident Rockstar
      2 years ago

      hi, Anonymous 
      try below

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