Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

PreviousMonth Calculation

Hello,   I have a very simple messure: TotalSalesBlue = SUMX(FILTER('Sales','Sales'[COLOR]="BLUE"),'Sales'[AMT])   Sales Table is connect with Date Table via DateID.   I'm trying to find Only ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    Explore relative dates filters

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

If you modify your TotalSalesBlue measure to this, do you get the correct result.

 

=CALCULATE(SUM('Sales'[AMT]),'Sales'[COLOR]="BLUE")

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Even after modifying the result I get all month


1- Changed messured as you suggested, still getting all months as shown below. I'm need to only get 9/30/2018 data.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Explore relative dates filters

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous

    You may try to this way as below:

    Step1:

    Add a year month column in date table

    Year Month Number = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] )

    Step2:

    modify the formula

    PreviousMonthBlueSlaes = CALCULATE ([TotalSalesBlue] , PREVIOUSMONTH('Date'[Date]), 'Date'[Year Month Number]=YEAR(TODAY())*100+MONTH(TODAY())-2)

    Result:

    BeforeAfter

     

    Best Regards,

    Lin