Skip to main content
cancel
Showing results for 
Search instead 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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors