Hi @bhuprakashs ,
Let's assume you have a Calendar table and a Customers fact table, and these tables are related by a date column (e.g., Order_Date in the Customers table). We want to calculate the sales for the exact same period in the previous month based on the latest sale date in the Customers table.
1- Create a new calculated column in the Calendar table to flag dates that are part of the same period as the previous month based on the most recent sales date.
isPast =
VAR lastSaleDate = MAX('Customers'[Order_Date]) // Get the latest sale date from the fact table
VAR lastSaleDatePY = EDATE(lastSaleDate, -1) // Get the same date from the previous month
RETURN
'Calendar'[Date] <= lastSaleDatePY // Check if the current date in the Calendar table is on or before the previous month's date
2- Next, we define a measure to calculate the sales for the same period, one month ago, ensuring that we're working with the exact same period (e.g., last month's sales).
Grand Total Sales Amount Last Month =
VAR total =
CALCULATE(
[Grand Total Sales Amount], // Replace with the measure that calculates your total sales
DATEADD('Calendar'[Date], -1, MONTH), // Shift the date by one month backward
'Calendar'[isPast] // Apply the filter to ensure we're only considering the same period last month
)
RETURN
total
I hope this helps!
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
Give it a like to show your appreciation!
✨ Thank you for contributing to our amazing Power BI community!
