To get the previous day's date as the current date in Power BI, you can use the TODAY() function to get today's date and then subtract one day from it to get the previous day. However, as you mentioned that there are duplicate dates, you may encounter issues when using simple date arithmetic.
To handle duplicate dates, you can use the following approach:
Create a calendar table: First, create a separate calendar table in Power BI that includes a unique list of all dates in your dataset, including duplicates if present.
Add a calculated column: In the calendar table, add a calculated column that calculates the previous day's date. You can use the EARLIER() function to refer to the current row's date in the calculation.
For example, if the name of the date column in your calendar table is "Date," and you want to create a new column called "PreviousDay," use the following DAX formula:
PreviousDay =
VAR CurrentDate = [Date]
RETURN
CALCULATE(
MAX('Calendar'[Date]),
'Calendar'[Date] < CurrentDate
)
This DAX formula calculates the maximum date that is less than the current date for each row, effectively giving you the previous day's date.
Use the new column in your visuals: Once you have the "PreviousDay" column in your calendar table, you can use it in your Power BI visuals and analyses. You can now filter data based on the previous day's date as needed
By using a separate calendar table and a calculated column, you can handle duplicate dates and ensure that the previous day's date is accurately calculated, even in cases where there are duplicates. This approach provides more flexibility and control over the date calculations in Power BI.
