none
PREVIOUSMONTH showing blank and not working RRS feed

  • Question

  • Hi 

    I have a data model with 3 tables Calendar, Revenue and Customer. Calendar and Revenue are joined on DateId column and Revenue and Customer are joined on CustomerID.

    I created a measure which shows total revenue. I want to create another measure which shows sales of previous month. But when I create the measure it shows blank. Calendar table looks like below

    DateId FiscalDate   FiscalMonth  FiscalQuarter  FiscalYear
    20150701  7/1/2015 7/1/2015   FY16-Q1  FY16
    20150702 7/2/2015 7/1/2015   FY16-Q1  FY16
    20150703 7/3/2015 7/1/2015   FY16-Q1  FY16
    20150704 7/4/2015 7/1/2015   FY16-Q1  FY16
    20150705 7/5/2015 7/1/2015   FY16-Q1  FY16
    20150706 7/6/2015 7/1/2015   FY16-Q1  FY16
    20150707 7/7/2015 7/1/2015   FY16-Q1  FY16

    When I create the PreviousMonth sales measure check it in I see results like below

    FiscalMonth PreviousMonth Sum of RevenueAmt PreviousMonth
    07/01/2015  
    08/01/2015 7/1/2015 $4.00
    09/01/2015 8/1/2015 $6.00
    10/01/2015 9/1/2015 $8.00
    11/01/2015 10/1/2015 $10.00
    12/01/2015 11/1/2015 $12.00
    01/01/2016 12/1/2015 $14.00
    02/01/2016 1/1/2016 $16.00
    03/01/2016 2/1/2016 $18.00
    04/01/2016 3/1/2016 $20.00
    05/01/2016 4/1/2016 $22.00
    06/01/2016 5/1/2016 $24.00
    07/01/2016 6/1/2016 $26.00
    08/01/2016 7/1/2016 $28.00
    09/01/2016 8/1/2016 $30.00
    10/01/2016 9/1/2016 $32.00
    11/01/2016 10/1/2016 $34.00
    12/01/2016 11/1/2016 $36.00
    01/01/2017 12/1/2016 $38.00
    02/01/2017

    Along with the PreviousMonth measure, need to create another measure which shows RevenueYTD (Revenue based on the selected year/month. The fiscal calendar starts July 1 and ends June 30).

    Please advice...



    Sunday, May 17, 2020 3:36 AM

All replies

  • Hi,

    As I checked your result, seems you got previous month sales right?

    How have you set YTD measure? It should be something like:

    CREATE MEMBER MEASURES.RevenueYTD

    AS AGGREGATE(YTD(), [Measures].[Sales Amount])


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 1, 2020 2:37 AM