Forum Discussion
Helper Vlymtd 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 calculation is
IF i didn't filter any month it need to show Curent month of LYMTD
IF I select any multiple month it need to show that selected month savel value
i try CALCULATE LYMTD :
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
- Kedar_Pande
Super User
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_onot working
- Kedar_Pande
Can you create a new date table:
New_DateTable = CALENDARAUTO()
- Ashish_Mathur
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi rajasekar_o
Can these current methods solve your problem? If yes, could you please mark them as solutions? This will help more users who are facing the same or similar difficulties. Thank you!
Best Regards,
Yulia Xu
