powerbidax

MTD LY and FULL MONTH based on month slicer selection

I have a date slicer Based on a date selection I'd like to see Sales for this year (TY) and last year (LY)

If current month is selected on a slicer then it should show MTD sales TY and MTD LY If previous months are selected then it should give me full month TY and LY actual sales

  1. Is there a way I can code DAX so that users can interact with slicers and see respective sales?
  2. Is there a way to save current month (February 2025) as selected by default in date slicer?

I tried with SAMEPERIODLASTYEAR but it gives me full month for Feb'24 - I just need MTD for current month. Current month = my last sales date in my fact table (Feb'13 2025 in this case)

Example

Solution

  • Try capturing the max date and then using that within an outer calculate:

    Your Measure = 
  var maxD = MAX(YourFactTable[Date])
  var isCurMonth = (EOMONTH(MAX(YourDateTable[Date]), 0) = EOMONTH(TODAY(), 0))

  var fullMonth = CALCULATE([Your TY Measure], DATEADD(YourDateTable[FMStartDate], -1, YEAR))

  var partialMonth = 
    CALCULATE(
      CALCULATE([Your TY Measure], DATEADD(YourDateTable[FMStartDate], -1, YEAR)), 
      YourDateTable[Date] <= maxD
    )

  var result = IF( isCurMonth, partialMonth, fullMonth)

  return result