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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arasshh
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
v-yingjl
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 ) !

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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