Forum Discussion

utkarsh9771's avatar
utkarsh9771
New Member
2 years ago
Solved

LMTD using date slicer selection

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.

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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's avatar
    Kaviraj11
    Solution Sage
    2 years ago

    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's avatar
      kpost
      Solution Sage
      2 years ago

      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.