Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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.
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.
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 @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.
Thanks for reply.
It is showing 1st Sep 2023 to 9th Sep 2023 data but i want to show only 9th Sep 2023 data
plz use below DAX:
SameDayLastMonth =
CALCULATE(
SUM('YourTable'[YourMeasure]),
FILTER('YourDateTable', 'YourDateTable'[DateColumn] = DATE(2023, 9, 9))
)
In this modified formula:
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.
Thanks for reply.
It is showing data from 1st Oct to 9th Oct but i want to show only 9th Sep 2023
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:
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, @Anonymous
try below
CALCULATE(
maxx(filter('calender table',CalendarTable[Date]'),[YourMeasure]),
DATEADD(CalendarTable[Date], -1, MONTH)
)
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |