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(Sal...
Need Help
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

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

    Hi ShashidharG,

     

    Have you resolved your issue? If my above advice is helpful to your scenario, please kindly mark it as an answer so that others having similar requirement can find the solution more easily. If you still have any question, please feel free to ask.

     

    Regards,
    Yuliana Gu