Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Month to date

    I want to compare the Month to date of current month with the month to date of last month  and the month to date of last 3 months.  i had used a formula like

 

MTD_current month  = var MTDs = min(DimDate[FirstDayOfMonth])

var MTDe = max(DimDate[LastDayOfMonth])
var val = CALCULATE(SUM(FactSales[TotalAmount]),FactSales[Invoice Date]>=MTDs,FactSales[Invoice Date]<=MTDe)
return val
 
MTD_Last month = 
var LMs = MIN(DimDate[FirstDayOfMonth])
var LMe = MAX(DimDate[LastDayOfMonth])
var val = CALCULATE(SUM(FactSales[TotalAmount]),FactSales[Invoice Date]>=LMs,FactSales[Invoice Date]<=LMe)
return val
 
MTD_Last 3 months = 
var startDate =DATEADD(FIRSTNONBLANK(DimDate[FirstDayOfMonth],"1"),-3,MONTH)
var endDate = DATEADD(FIRSTNONBLANK(DimDate[LastDayOfMonth],"1"),-3,MONTH)
var val = CALCULATE(SUM(FactSales[TotalAmount]),FactSales[Invoice Date]>=startDate,FactSales[Invoice Date]<=endDate)
return val
 
But when i use it i am getting same values for MTD_curent month and MTD_last month and for MTD_Last 3 month i am not getting any values please provide me an alternative solution or with correct formula.
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi @sathish_kumar,

     

    Please refer to the following measures to modify your measures and see if the result achieve your expectation, if not, kindly share your sample data and excepted result to me if you don't have any Confidential Information.

    MTD_current month =

    CALCULATE (

        SUM ( 'FactSales'[TotalAmount] ),

        FILTER (

           ALLSELECTED ( 'FactSales' ),

            FORMAT ( 'FactSales'[Invoice Date], "YYYYMM" )

                = FORMAT ( MAX ( 'FactSales'[Invoice Date] ), "YYYYMM" )

        )

    )

    MTD_Last month =

    CALCULATE (

        SUM ( 'FactSales'[TotalAmount] ),

        FILTER (

            ALLSELECTED ( 'FactSales' ),

            FORMAT ( 'FactSales'[Invoice Date], "YYYYMM" )

                = FORMAT ( EDATE ( MAX ( 'FactSales'[Invoice Date] ), -1 ), "YYYYMM" )

        )

    )

    MTD_Last 3 months =

    CALCULATE (

        SUM ( 'FactSales'[TotalAmount] ),

        FILTER (

            ALLSELECTED ( 'FactSales' ),

            FORMAT ( 'FactSales'[Invoice Date], "YYYYMM" )

                = FORMAT ( EDATE ( MAX ( 'FactSales'[Invoice Date] ), -3 ), "YYYYMM" )

        )

    )

    Result would be shown as below:

    BTW, Pbix as attached, hopefully works for you.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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