Reply
rajasekar_o
Helper V
Helper V

LYMTD AND LYYTD Calculate.

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 DateItemQtyAmount
01-01-2023ITEM A5670
03-01-2023ITEM A6804
03-01-2023ITEM B4536
30-01-2023ITEM A3402
20-02-2023ITEM B7938
06-09-2023ITEM A3402
07-09-2023ITEM B7938
01-10-2023ITEM B4536
06-10-2023ITEM A81072
06-10-2023ITEM B4536
30-10-2023ITEM A81072
05-09-2024ITEM B3402
09-09-2024ITEM A7938
01-10-2024ITEM B5670
02-10-2024ITEM A81072
04-10-2024ITEM B3402
2 ACCEPTED SOLUTIONS
rajendraongole1
Super User
Super User

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")
)

rajendraongole1_0-1728279553776.png

 

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

rajendraongole1_1-1728279604773.png

 

rajendraongole1_2-1728279629302.png

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
 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

ThxAlot
Super User
Super User

ThxAlot_2-1728285121792.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

3 REPLIES 3
ThxAlot
Super User
Super User

ThxAlot_2-1728285121792.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



rajendraongole1
Super User
Super User

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")
)

rajendraongole1_0-1728279553776.png

 

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

rajendraongole1_1-1728279604773.png

 

rajendraongole1_2-1728279629302.png

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
 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





bhanu_gautam
Super User
Super User

@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()))
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)