Forum Discussion

PBIBeginner2022's avatar
PBIBeginner2022
Icon for Helper III rankHelper III
4 years ago
Solved

DAX : Sum previous month automatically

Hello,

 

I would like to translate this into DAX language:

 

I have a column calculated with the value of each item sold and a column with the date of sale of each item.
I would like to create a measure that gives me the total value sold for the month before last. So the current month minus -1.

 

For example, we are in June, I would like to know the total value sold in May. When we are in July, I would like my measure to automatically calculate the total value sold in June.

 

Thanks in advance

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi PBIBeginner2022 ，

     

    This formula should work.

    Pre_sales =
CALCULATE (
    SUM ( 'Table'[Price] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        FORMAT ( 'Table'[Date], "YYYYMM" ) = FORMAT ( EDATE ( TODAY (), -1 ), "YYYYMM" )
    )
)

     

    Best Regards,

    Jay

13 Replies

  • Hariharan_R's avatar
    Hariharan_R
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi,

    Try below measure.

    Previous Month Sales = CALCULATE(SUM(Sales[Sales]),PREVIOUSMONTH('Date'[Date]))
     

     

    • PBIBeginner2022's avatar
      PBIBeginner2022
      Icon for Helper III rankHelper III
      4 years ago

      Hi Hariharan_R 

       

      When I try this, it's wrote "vide" in French (void I Think).

      CALCULATE(SUM(......) is good but after to select only the month minus  1 it's complicate because I have date with this format "01/04/2022" for exemple between  year 1988 and 2024 ....

    • bettyfish's avatar
      bettyfish
      New Member
      2 years ago

      when i try this the very first month, instead of being blank, includes the total of all rows

      beg bal BS = var result = calculate([FTP-TTD],PREVIOUSMONTH('tbl_Calendar'[Date]))
      would you have any idea what i can be doing wrong??

      thanks!

      betty

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        Hi,

        Share the download link of the PBI file and show the problem there very clearly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi PBIBeginner2022 ,

     

    Please try these formulas.

    Column:

    Pre_sales =
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[date] = EDATE ( EARLIER ( 'Table'[date] ), -1 )
    ),
    'Table'[value]
)

    Measure:

    Pre_sales =
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[date] = EDATE ( SELECTEDVALUE ( 'Table'[date] ), -1 )
    ),
    'Table'[value]
)

    If it doesn't work, please share some sample data and expected result so that we could test the formula.

     

    Best Regards,

    Jay 

    • PBIBeginner2022's avatar
      PBIBeginner2022
      Icon for Helper III rankHelper III
      4 years ago

      Hi,

       

      I tried your measurement, but I still have the same problem, no value is displayed. Here is a data set:

       

      I want the sum of the actual month -1. So we are in June, I want the sum of price in May.

       

      ArticlePriceDate
      ART00014311/06/2022
      ART000223519/05/2022
      ART00034312/06/2022
      ART000498723/05/2022
      ART0005413/06/2022
      ART00065402/06/2022
      ART0007711/08/2022
      ART00086534208/06/2022
      ART00098713/05/2022
      ART001034209/05/2022
      ART0011007/06/2022
      ART0012728/05/2022
      ART00133430/04/2022
      ART00146518/05/2022
      ART00153213/07/2022
      ART0016914/05/2022
      ART0017210/08/2022
      ART00184305/06/2022
      ART001923519/04/2022
      ART00204325/08/2022
      ART002198711/05/2022
      ART0022412/07/2022
      ART00235410/05/2022
      ART0024724/08/2022
      ART00256534230/06/2022
      ART00268706/05/2022
      ART002734224/04/2022
      ART0028021/06/2022
      ART0029704/05/2022
      ART00303405/05/2022
      ART00316517/05/2022
      ART00329605/07/2022
      ART003312726/05/2022
      ART003415825/08/2022
      ART003518931/07/2022
      ART003622026/08/2022
      ART003725103/07/2022
      ART003828219/07/2022
      ART003931318/05/2022
      ART004034414/06/2022
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi PBIBeginner2022 ,

         

        Thank you for the data.
        Please try this formula:

        Pre_sales =
CALCULATE (
    SUM ( 'Table'[Price] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date].[MonthNo]
            = SELECTEDVALUE ( 'Table'[Date].[MonthNo] ) - 1
    )
)

         

        Best Regards,

        Jay