Forum Discussion

Nanakwame's avatar
Nanakwame
Helper 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 l...
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. 

TheoC's avatar
TheoC
Community 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
    Helper 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. 

    • TheoC's avatar
      TheoC
      Community Champion
      2 years ago

      tyjames05 glad it helped, mate!  

  • Nanakwame's avatar
    Nanakwame
    Helper 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
      Community 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.

       

      • JG007's avatar
        JG007
        Regular Visitor
        3 years ago

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