lymtd calculate

I have sales data from 1-1-2022 to 25-09-2024 Sales Table have the column invno,invdate,item,qty,Netamount i have calender table start date :1-1-2024end date: 31-12-2024 AND MTD calculatio...

Need Help

Tips and Tricks

Tutorial Requests

Anonymous
1 year ago
Hi rajasekar_o

Thanks for the reply from Kedar_Pande and Ashish_Mathur .

Do you need to show the sum for the same time period last year if you select YEAR and MONTH, and the value for the current month of last year if you don't? If I understand correctly, the following test is for your reference:

My sample:

Calendar table:

Sales table:

Create a measure as follow:

LMTD = VAR _year = SELECTEDVALUE('Calendar'[YEAR]) RETURN IF( ISFILTERED('Calendar'[YEAR]) && ISFILTERED('Calendar'[month]), CALCULATE(SUM(sales[Amount]), FILTER(sales, YEAR([Inv Date]) = _year - 1 && [Inv Date].[Month] IN VALUES('Calendar'[month]))), CALCULATE(SUM(sales[Amount]), FILTER(sales, YEAR([Inv Date]) = _year - 1 && MONTH([Inv Date]) = MONTH(TODAY()))) )

Output:

After my testing, if you use SAMEPERIODLASTYEAR, this requires that you have the same period of last year in your calendar table, like the screenshots below:

When the same date last year does not exist in the calendar table:

When the same date last year exists in the calendar table:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lymtd calculate.pbix69 KB

Kedar_Pande

Super User

1 year ago

Create a new measure for LY MTD sales:

LY MTD Sale =
VAR _year = VALUES('Date'[Year])
VAR _month = VALUES('Date'[Month No])
RETURN
IF(
ISFILTERED('Date'[Year]) && ISFILTERED('Date'[Month No]),
CALCULATE(
SUM('Sales'[Net Amount]),
FILTER(
'Sales',
YEAR('Sales'[Inv Date]) = _year - 1 && // Adjust year for LY
MONTH('Sales'[Inv Date]) IN _month
)
),
TOTALMTD(
SUM('Sales'[Net Amount]),
SAMEPERIODLASTYEAR('Date'[Date])
)
)

rajasekar_o

Helper V

1 year ago

not working

Kedar_Pande
Super User
1 year ago
Can you create a new date table:
New_DateTable = CALENDARAUTO()
- rajasekar_o
  Helper V
  1 year ago
  New_DateTable = CALENDARAUTO()
  if i use this its create date upto 25-09-2024
  its not correct
  - Kedar_Pande
    Super User
    1 year ago
    Use the below DAX to create a new date table:
    DateTable =
    VAR MinYear = YEAR(MIN('YourTable'[Date]))
    VAR MaxYear = YEAR(MAX('YourTable'[Date]))
    RETURN
    ADDCOLUMNS(
    CALENDAR(
    DATE(MinYear, 1, 1),
    DATE(MaxYear, 12, 31)
    ))

Forum Discussion

lymtd calculate