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
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