Previous Month to Date Calculation based on current date

[Nanakwame]

Hi All,

I have a function to calculate the PMTD (previous month to date) sales using the function below 

PreviousMTD = TOTALMTD( SUM(Table[Value]) , DATEADD('Date'[Date], -1 , MONTH) )

I will like to to take into consderation the number of days for the current month. 

For instance for Oct 4, the PTMD will only calculate 4 days in september since we are only 4 days in october. 

Any idea how to achieve this?

Thank you 

[TheoC · Accepted solution]

Hi @Nanakwame 

If I am reading correctly, you are quite close.  I recommend creating three measures. The first is to sum the relevant values. The second to sum only the portion of the Current Month To Date.  The third is to mimic the position of the Current Month To Date measure.

1. Sum Value = Sum ( 'Table'[Value] )
2. Total CurMthTD = CALCULATE ( [Sum Value] , DATESMTD ( 'Date'[Date] ) )
3. Total LstMthTD = CALCULATE ( [Total CurMthTD] , DATEADD ('Date'[Date] , -1 , MONTH ) )

Hope this helps! 🙂

[TheoC · Accepted solution]

Hi @Nanakwame 

If I am reading correctly, you are quite close.  I recommend creating three measures. The first is to sum the relevant values. The second to sum only the portion of the Current Month To Date.  The third is to mimic the position of the Current Month To Date measure.

1. Sum Value = Sum ( 'Table'[Value] )
2. Total CurMthTD = CALCULATE ( [Sum Value] , DATESMTD ( 'Date'[Date] ) )
3. Total LstMthTD = CALCULATE ( [Total CurMthTD] , DATEADD ('Date'[Date] , -1 , MONTH ) )

Hope this helps! 🙂

[tyjames05]

I found this response very helpful. I've been looking for this answer for awhile now.. thank you so much. It works perfectly for me. 

[TheoC]

@tyjames05 glad it helped, mate!  

[Nanakwame · Accepted solution]

Hi Theo,

i tried the approach you provided and it seems like its calculating the 6 days for october comparing all of september. I will prefer it compare it to only 6 days in september as well and so on. 

[v-yingjl]

Hi @Nanakwame ,

You can try these measures to compare:

CurrentMonthValue = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Date',
        'Date'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
            && 'Date'[Date] <= TODAY ()
    )
)

PreviousMonthValue = 
VAR currentmonthstart =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR currentday =
    TODAY ()
VAR currentdiff =
    DATEDIFF ( currentmonthstart, currentday, DAY )
VAR previousmonthstart =
    EOMONTH ( TODAY (), -2 ) + 1
VAR PreviousMonthValue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Date',
            'Date'[Date] >= previousmonthstart
                && 'Date'[Date] <= previousmonthstart + currentdiff
        )
    )
RETURN
    PreviousMonthValue

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JG007]

I can't tell how long I've ben looking for this solution - Thanks a lot