Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ShashidharG
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" ) ) 

 

PriorParam.PNG

 

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
v-yulgu-msft
Microsoft Employee
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.

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.

PriorMonth.PNG

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

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.

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

3.PNG

 

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.