Forum Discussion

Nanakwame's avatar
Nanakwame
Icon for Helper II rankHelper II
4 years ago
Solved

Previous Month to Date Calculation based on current date

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 

data protection
Need Help
Show and Tell
Tips and Tricks
  • TheoC's avatar
    TheoC
    4 years ago

    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! 🙂

  • Nanakwame's avatar
    Nanakwame
    4 years ago

    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. 

6 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      tyjames05
      Icon for Helper I rankHelper I
      2 years ago

      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. 

    • Nanakwame's avatar
      Nanakwame
      Icon for Helper II rankHelper II
      4 years ago

      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's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        4 years ago

        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.