Forum Discussion

ShashidharG's avatar
ShashidharG
Frequent Visitor
9 years ago

Prior Sales in Current & Previous month data

Hi,

 

Using this calculation i get an option to filter Current Month and Previous month data. Its intended to get Prior Month data in a column as in the snapshot !!!  

 

TimePeriod = IF(MONTH(SalesHist[BillDate])=MONTH(TODAY()) && YEAR(SalesHist[BillDate])=YEAR(TODAY()) , "CurrentMonth", IF(MONTH(SalesHist[BillDate])=(MONTH(TODAY())-1) && YEAR(SalesHist[BillDate])=YEAR(TODAY()), "PreviousMonth","Older" ) ) 

 

 

PriorMOnthSales = IF(SalesHist[TimePeriod] ="CurrentMonth",CALCULATE(SUM(SalesHist[Net]), PREVIOUSMONTH(SalesHist[BillDate])) ,0)

 

both the PREVIOUSMONTH & Calculate(Sum(Sales),Parallelperiod(BillDate,-1,Month))

didn't work in this case.... 

 

any suggestions...???

Need Help

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi ShashidharG,

     

    According to current description, I am confused about your expected result. From the snapshot you provided, I noticed that you used two tables to display data, what did that mean?

     

    Also, if applying this formula: PriorMOnthSales = IF(SalesHist[TimePeriod] ="CurrentMonth",CALCULATE(SUM(SalesHist[Net]), PREVIOUSMONTH(SalesHist[BillDate])) ,0), it seems that the expected result of PriorMOnthSales in table "PreviousMonth" should be 0 rather than 45k and 55k.

     

    Please elaborate your scenario with some sample data (source table view) and visual design.

     

    Regards,
    Yuliana Gu 

    • ShashidharG's avatar
      ShashidharG
      Frequent Visitor
      9 years ago

      Definitely..

      In my earlier post Adding Parameter in my data shows only Current month data on CurrentMonth selection, and PreviousMonth for prior month data.

       

      Its is a single column PriorMonth to be shown in the CurrentMonth data, as on parameter selection CurrentMonth, and the same way.. a single column PriorMonth to be shown in the PreviousMonth data(Prior to previous month), as on parameter selection PreviousMonth.  Hope you got my point here...

       

      Its as in the 2 snapshots earlier Prior month common to both Current and previous month data.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi ShashidharG,

         

        Sorry for the delay.

         

        In my test, I had a table view 'Prior Sales', containing two columns [Date] and [Sales]. Then, I created two calculated columns using below DAX formula:

        Sum Sales =
CALCULATE (
    SUM ( 'Prior Sales'[Sales] ),
    ALLEXCEPT (
        'Prior Sales',
        'Prior Sales'[Date].[Year],
        'Prior Sales'[Date].[MonthNo]
    )
)

Previous Month sales =
IF (
    'Prior Sales'[Date].[MonthNo] = 1,
    LOOKUPVALUE (
        'Prior Sales'[Sum Sales],
        'Prior Sales'[Date].[Year], 'Prior Sales'[Date].[Year] - 1,
        'Prior Sales'[Date].[MonthNo], 'Prior Sales'[Date].[MonthNo] + 11
    ),
    LOOKUPVALUE (
        'Prior Sales'[Sum Sales],
        'Prior Sales'[Date].[Year], 'Prior Sales'[Date].[Year],
        'Prior Sales'[Date].[MonthNo], 'Prior Sales'[Date].[MonthNo] - 1
    )
)

         

        If you still have any quesion, please share your source table for fuether analysis.

         

        Regards,
        Yuliana Gu