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.

9 Replies

  • 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)
        )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for reply.

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

  • 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

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

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.