Forum Discussion

rajasekar_o's avatar
rajasekar_o
Helper V
1 year ago
Solved

MTD calculation

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 i Calculate MTD  ...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi rajasekar_o 

     

    Try this:

     

    LYMTD Sales = 
VAR _year = SELECTEDVALUE('Calendar'[Year])
VAR _month = VALUES('Calendar'[Month])
RETURN
IF(
    ISFILTERED('Calendar'[Year]) && ISFILTERED('Calendar'[Month]),
    CALCULATE(
        SUM('Sales'[Netamount]),
        FILTER(
            'Sales',
            YEAR('Sales'[invdate]) = _year - 1
            &&
            MONTH('Sales'[invdate]) IN _month
        )
    ),
    TOTALMTD(
        SUM('Sales'[Netamount]), 
        'Sales'[invdate]
    )
)

     

     

    Regards,

    Nono Chen

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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi rajasekar_o 

 

Try this:

 

LYMTD Sales = 
VAR _year = SELECTEDVALUE('Calendar'[Year])
VAR _month = VALUES('Calendar'[Month])
RETURN
IF(
    ISFILTERED('Calendar'[Year]) && ISFILTERED('Calendar'[Month]),
    CALCULATE(
        SUM('Sales'[Netamount]),
        FILTER(
            'Sales',
            YEAR('Sales'[invdate]) = _year - 1
            &&
            MONTH('Sales'[invdate]) IN _month
        )
    ),
    TOTALMTD(
        SUM('Sales'[Netamount]), 
        'Sales'[invdate]
    )
)

 

 

Regards,

Nono Chen

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