LYMTD AND LYYTD Calculate.

[rajasekar_o]

Hi team 
i have 2 year sales data 
i want to calculate LYMTD and LYYTD  Till date
(Eg: LYMTD SALES NEED TO CALCULATE 1-10-23 TO 07-10-23
LYYTD SALES NEED TO CALCULATE 01-01-23 TO 07-10-23 )

Inv Date	Item	Qty	Amount
01-01-2023	ITEM A	5	670
03-01-2023	ITEM A	6	804
03-01-2023	ITEM B	4	536
30-01-2023	ITEM A	3	402
20-02-2023	ITEM B	7	938
06-09-2023	ITEM A	3	402
07-09-2023	ITEM B	7	938
01-10-2023	ITEM B	4	536
06-10-2023	ITEM A	8	1072
06-10-2023	ITEM B	4	536
30-10-2023	ITEM A	8	1072
05-09-2024	ITEM B	3	402
09-09-2024	ITEM A	7	938
01-10-2024	ITEM B	5	670
02-10-2024	ITEM A	8	1072
04-10-2024	ITEM B	3	402

[rajendraongole1 · Accepted solution]

Hi @rajasekar_o - First, ensure you have a proper Date table in your model with continuous dates. You can create one using DAX.

DateTable =

ADDCOLUMNS (

    CALENDAR (DATE(2022, 1, 1), DATE(2024, 12, 31)),  // Adjust this range as per your data

    "Year", YEAR([Date]),

    "Month", MONTH([Date]),

    "Day", DAY([Date]),

    "MonthYear", FORMAT([Date], "MMM YYYY"),

    "IsToday", [Date] = TODAY(),

    "Year-Month", FORMAT([Date], "YYYY-MM")

)

create a relationship between date table and sales table as below:

LYMTD Sales =

CALCULATE (

    SUM('invdqty'[Amount]),

    SAMEPERIODLASTYEAR(DATESMTD('DateTable'[Date]))

)

you can replace the table name as per your model

LYYTD Sales =

CALCULATE (

    SUM('invdqty'[Amount]),

    SAMEPERIODLASTYEAR(DATESYTD('DateTable'[Date], "31-12"))

)

Hope this works

[ThxAlot · Accepted solution]

[rajendraongole1 · Accepted solution]

Hi @rajasekar_o - First, ensure you have a proper Date table in your model with continuous dates. You can create one using DAX.

DateTable =

ADDCOLUMNS (

    CALENDAR (DATE(2022, 1, 1), DATE(2024, 12, 31)),  // Adjust this range as per your data

    "Year", YEAR([Date]),

    "Month", MONTH([Date]),

    "Day", DAY([Date]),

    "MonthYear", FORMAT([Date], "MMM YYYY"),

    "IsToday", [Date] = TODAY(),

    "Year-Month", FORMAT([Date], "YYYY-MM")

)

create a relationship between date table and sales table as below:

LYMTD Sales =

CALCULATE (

    SUM('invdqty'[Amount]),

    SAMEPERIODLASTYEAR(DATESMTD('DateTable'[Date]))

)

you can replace the table name as per your model

LYYTD Sales =

CALCULATE (

    SUM('invdqty'[Amount]),

    SAMEPERIODLASTYEAR(DATESYTD('DateTable'[Date], "31-12"))

)

Hope this works

[bhanu_gautam]

@rajasekar_o , First please make sure you have date table then you can create measure 

LYMTD Sales =
CALCULATE (
SUM(Sales[Amount]),
DATESBETWEEN (
'DateTable'[Date],
DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1),
DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))
)
)

And one more 

LYYTD Sales =
CALCULATE (
SUM(Sales[Amount]),
DATESBETWEEN (
'DateTable'[Date],
DATE(YEAR(TODAY()) - 1, 1, 1),
DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))
)
)