Forum Discussion

rajasekar_o's avatar
rajasekar_o
Icon for Helper V rankHelper V
1 year ago

LAST YEAR MTD

Hi team
I have sales data from 
1-1-2022 to 27-09-2024
Sales Table  have the column
invno,invdate,item,qty,Netamount
i have calender table 
start date :1-1-2024 end date: 31-12-2024
i wnat to calculate MTD AND LYMTD 

MTD (Month-to-Date) Conditions:

  1. No month filter applied: If no month is selected, the calculation should return the sales for the current month (e.g., sales from September 1 to September 27 for September).
  2. Multiple months selected: If multiple months are selected (e.g., January, March, May), the calculation should return the sales for the selected months.

LY MTD (Last Year Month-to-Date) Conditions:

  1. No month filter applied: If no month is selected, the calculation should return the sales for the same period in the previous year (e.g., sales from September 1 to September 27 of the previous year).
  2. Multiple months selected: If multiple months are selected (e.g., January, March, May), the calculation should return the sales for the same months in the previous year.

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • dharmendars007's avatar
    dharmendars007
    Icon for Memorable Member rankMemorable Member
    1 year ago

    Hello rajasekar_o , 

     

    For calculating the MTD if moth is selected and not selected you can pass the IF condition with filter conditon like below..

     

    1. MTD

    MTD Sales =
    IF (
    ISFILTERED(Calendar[Month]),
    CALCULATE(SUM(SalesTable[Netamount]),
    DATESINPERIOD(Calendar[Date],MAX(Calendar[Date]),-DAY(MAX(Calendar[Date])) + 1,MONTH)),
    CALCULATE(SUM(SalesTable[Netamount]),
    DATESBETWEEN(Calendar[Date],STARTOFMONTH(TODAY()),TODAY())))

     

    For LY MTD you need to change the measure by passing the Sameperiodlastyear function..

     

    2. LY MTD

    LY MTD Sales =
    IF (
    ISFILTERED(Calendar[Month]),
    CALCULATE(SUM(SalesTable[Netamount]),
    SAMEPERIODLASTYEAR(DATESINPERIOD(Calendar[Date],MAX(Calendar[Date]),-DAY(MAX(Calendar[Date])) + 1,MONTH))),
    CALCULATE(SUM(SalesTable[Netamount]),
    SAMEPERIODLASTYEAR(DATESBETWEEN(Calendar[Date],STARTOFMONTH(TODAY()),TODAY()))))

     

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

    • rajasekar_o's avatar
      rajasekar_o
      Icon for Helper V rankHelper V
      1 year ago

      dharmendars007 

      MTD =
      IF (
      ISFILTERED('date'[Month]),
      CALCULATE(SUM(sales[Amount]),
      DATESINPERIOD('date'[Date],MAX(date[Date]),-DAY(MAX(date[Date])) + 1,MONTH)),
      CALCULATE(SUM(sales[Amount]),
      DATESBETWEEN(date[Date],STARTOFMONTH(TODAY()),TODAY())))
      ERROR :
      The syntax for '[Date]' is incorrect. (DAX(IF (ISFILTERED('date'[Month]),CALCULATE(SUM(sales[Amount]),DATESINPERIOD('date'[Date],MAX(date[Date]),-DAY(MAX(date[Date])) + 1,MONTH)),CALCULATE(SUM(sales[Amount]),DATESBETWEEN(date[Date],STARTOFMONTH(TODAY()),TODAY()))))).
  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    1. Create MTD Measure-

    MTD Sales = 
    VAR SelectedMonths = VALUES('Calendar'[Month]) // Get selected months
    VAR CurrentMonth = MONTH(TODAY()) // Current month
    VAR CurrentYear = YEAR(TODAY()) // Current year

    RETURN
    IF(
    ISFILTERED('Calendar'[Month]),
    CALCULATE(
    SUM(Sales[Netamount]),
    FILTER(
    Sales,
    MONTH(Sales[invdate]) IN SelectedMonths &&
    YEAR(Sales[invdate]) = CurrentYear
    )
    ),
    CALCULATE(
    SUM(Sales[Netamount]),
    FILTER(
    Sales,
    MONTH(Sales[invdate]) = CurrentMonth &&
    YEAR(Sales[invdate]) = CurrentYear
    )
    )
    )

     2. Create LY MTD Measure-

    LY MTD Sales = 
    VAR SelectedMonths = VALUES('Calendar'[Month]) // Get selected months
    VAR LastYear = YEAR(TODAY()) - 1 // Last year

    RETURN
    IF(
    ISFILTERED('Calendar'[Month]),
    CALCULATE(
    SUM(Sales[Netamount]),
    FILTER(
    Sales,
    MONTH(Sales[invdate]) IN SelectedMonths &&
    YEAR(Sales[invdate]) = LastYear
    )
    ),
    CALCULATE(
    SUM(Sales[Netamount]),
    FILTER(
    Sales,
    MONTH(Sales[invdate]) = MONTH(TODAY()) &&
    YEAR(Sales[invdate]) = LastYear
    )
    )
    )


    Your Kudos/Likes are much appreciated!
    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
    Regards,
    Kedar Pande
    www.linkedin.com/in/kedar-pande