cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Calculate Previous Month Sales w

Hi All,

 

I am trying to calculate the Total Sales Rev where I have these columns

 

Currentfiscalmonth = True or False

CurrentFiscalyear = True or False

Fiscalmonth = 1 - 12

 

now what I want to do is to get current month number for this fiscal year and then minus it one to get last month and then sum the total sales how can I accomplish that?

4 REPLIES 4
Community Support
Community Support

Hi @arasshh ,

1. If you want to create last month total sales, you can create a calendar date table first and create a relationship between it and the fact table.

Calendar = CALENDARAUTO()

2. To create the measure to get the amount of the previous month.

Last month sales = 
CALCULATE(
    SUM('Table'[Sales]),
    PREVIOUSMONTH('Calendar'[Date])
)

last month sales.png

Here is the sample file that hopes to help you, please try it: Last month sales.pbix 

 

If not help, could you please consider providing some sample data and expected output for further discussion?

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

@v-yingjl thank you for the answer!

I'm working on company data and we already have calendar, the thing is that it's fiscal calendar which is different from the calendar year.

so I can't use date functions. the columns I have, the ones I mentioned in my message.

I'm trying to return the previous fiscal month/this number using the currentmonth column (which is True or False ) !

@v-yingjl  thanks for the reply!

 

I am working on the company data and we already have calendar, the thing is it is fiscal calendar which is different from calendar year.

 

so I cannot use date functions. the columns I have, to work with are the ones I mentioned in my message.

 

I am trying to return the previous/this fiscal month number using currentmonth column ( which is True or False ) !

Advocate I
Advocate I

Hey @arasshh 

You could use the following for the % change in sales from last month to current month

_Last Month Sales Vs Current =
var month_minus_1 = CALCULATE(Sum(Sales),DATEDIFF(DATEVALUE,UTCTODAY,MONTH)=-1)
var month_current = CALCULATE ((Sum(Sales),DATEDIFF(DATEVALUE,UTCTODAY,MONTH)=0)
return DIVIDE(month_current-month_minus_1,month_minus_1)

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors