cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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...???

4 REPLIES 4
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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.

Microsoft Employee

Hi @ShashidharG,

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.