LMTD using date slicer selection

[utkarsh9771]

Hello Experts!

Need help creating a logic
Need to get sales for last month based on the user date selection

For E.g. If I have selected 01-06-2024 and 12-06-2024 in my date slicer, I should get sales from 1th to 12th May.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Date column of the Fact Table to the Date column of the Calendar Table.  To your slicer, drag Date from the Calendar Table and select a range of dates.  Write this measure

Sales = sum(Data[Amount])

Sales in same period last month = calculate([Sales],datesbetween(calendar[date],edate(min(calendar[date]),-1),edate(max(calendar[date]),-1)))

Hope this helps.

[Kaviraj11]

Hi,

You will need to follow these steps:

1. Create a Date Table: Ensure you have a date table in your Power BI model that includes all dates, and it’s marked as a date table.

2. Establish Relationships: Make sure there’s a relationship between your date table and your sales data table based on the date fields.

3. Create a New Measure: 

Sales Last Month =
VAR SelectedStartDate = MIN('Date'[Date])
VAR SelectedEndDate = MAX('Date'[Date])
VAR StartOfLastMonth = EOMONTH(SelectedStartDate, -2) + 1
VAR EndOfLastMonth = EOMONTH(SelectedStartDate, -1)
RETURN
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
ALL('Date'),
'Date'[Date] >= StartOfLastMonth && 'Date'[Date] <= EndOfLastMonth
)
)

[kpost]

I think your solution is going to show results for the entire prior month, whereas the example in the question was if the user selects from the 1st to the 12th of June, the measure should display data from the 1st to the 12th of May.