cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lucasd Regular Visitor
Regular Visitor

Total sales of last year

Dear community,

 

I'm working on a finance report and I need a few calculations:

report

 

I suceeded on most of them but I don't know how to calculate dinamically the Annual Sales FY-1 and FY-2.

My FY is from 01/10 to 30/09.

 

My calculation today is:

 

Annual Sales FY-1 = CALCULATE (
SUM ( Sales[Sales] );DATESBETWEEN('Date'[Date];[FY-1 Start];[FY-1 End]))
FY-1 Start = DATE(YEAR(TODAY())-2;10;1)
FY-1 End = DATE(YEAR(TODAY())-1;9;30)
 
Right now it works OK, but if the user filters 2018 or 2017 no. My current workaround is a switch to show the value of that year and numerous DAX formulas.
 
Annual Sales FY-1 switch =
SWITCH(TRUE();
[SelectedYear]="2019"; [Annual Sales 2018];
[SelectedYear]="2018"; [Annual Sales 2017];
[SelectedYear]="2017"; [Annual Sales 2016];
[SelectedYear]="2016"; [Annual Sales 2015];
"etc"
)
 
I've read the forum and the Dax Patterns but I could not find a way to filter the values automatically.
 
Is it possible to make the bold values be changed dinamically based on the year I select? 
FY-1 Start = DATE(YEAR(TODAY())-2;10;1)
FY-1 End = DATE(YEAR(TODAY())-1;9;30)
 
Could you please give me a hint?
1 ACCEPTED SOLUTION

Accepted Solutions
tex628 New Contributor
New Contributor

Re: Total sales of last year

FY-1 =

Var tDay = Today()
Var mDay = MAX(Calendar[Date])

Var t_sDate = DATE(YEAR(tDay)-2;10;1)
Var t_eDate = DATE(YEAR(tDay)-1;9;30)
Var t_dDate = DATE(YEAR(tDay);9;30)
Var t_sDate_2 = DATE(YEAR(tDay)-1;10;1)
Var t_eDate_2 = DATE(YEAR(tDay);9;30)

Var m_sDate = DATE(YEAR(mDay)-2;10;1)
Var m_eDate = DATE(YEAR(mDay)-1;9;30)
Var m_dDate = DATE(YEAR(mDay);9;30)
Var m_sDate_2 = DATE(YEAR(mDay)-1;10;1)
Var m_eDate_2 = DATE(YEAR(mDay);9;30)

Return 

IF(mDay>tDay;

IF(tDay=>t_dDate;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>t_sDate_2;
Calendar[Date]=<t_eDate_2
)

;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>t_sDate;
Calendar[Date]=<t_eDate
)
)

;

IF(mDay=>m_dDate;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>m_sDate_2;
Calendar[Date]=<m_eDate_2
)

;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>m_sDate;
Calendar[Date]=<m_eDate
)
)
)

It's getting a little messy but i hope it's correct! 🙂

View solution in original post

9 REPLIES 9
bhpage Member
Member

Re: Total sales of last year

Could you try re-attaching you image? I can't access it.

 

Thanks,

Ben

Super User
Super User

Re: Total sales of last year

Write a measure just to calc total sales

Total Sales = SUM ( Sales[Sales] )

Then you PY-1 is just

_PY-1 sales = CALCULATE( [Total Sales] , DATEADD ( DATESYTD ('Date'[Date]) , -1 , YEAR ) )

PY-2 is

_PY-2 sales = CALCULATE( [Total Sales] , DATEADD ( DATESYTD ('Date'[Date]) , -2 , YEAR ) )

py.jpg

lucasd Regular Visitor
Regular Visitor

Re: Total sales of last year

Capture.JPG

lucasd Regular Visitor
Regular Visitor

Re: Total sales of last year

Hi, that's not the idea.

From 01/10/18 until 30/09/19, my last year sales should be from 01/10/2017 until 30/09/2018 and so on.

In your example, in 2019 I would have sales from 01/01/18 until 31/12/18.

lucasd Regular Visitor
Regular Visitor

Re: Total sales of last year

Still not the ideal workaround but I'm getting there.

 

I created a filtered sales table based on FY:

 

Capture.JPG

 

Then I'm able to filter results based on the year the user selects:

 

Capture.JPG

 

I would like to have all that in a simple argument with VAR but I can't figure out how.

 

I appreciate your help. Thanks

tex628 New Contributor
New Contributor

Re: Total sales of last year

FY-1 =

Var tDay = Today()
Var mDay = MAX(Calendar[Date])
Var t_sDate = DATE(YEAR(tDay)-2;10;1)
Var t_eDate = DATE(YEAR(tDay)-1;9;30)
Var m_sDate = DATE(YEAR(mDay)-2;10;1)
Var m_eDate = DATE(YEAR(mDay)-1;9;30)

Return 

IF(mDay>tDay;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>t_sDate;
Calendar[Date]=<t_eDate
)

;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>m_sDate;
Calendar[Date]=<m_eDate
)
)
lucasd Regular Visitor
Regular Visitor

Re: Total sales of last year

The code is good but there is still a need to be able to filter by year and month.

 

In Septembre 2018 I should view sales from 01/10/2016 until 30/09/2017.
In October 2018 I should view sales from 01/10/2017 until 30/09/2018.

 

Today I don't have this in the your code. I can use a switch as a workaround, but would you be able to advise again? Thanks

 

october.JPGseptember.JPG

 

tex628 New Contributor
New Contributor

Re: Total sales of last year

FY-1 =

Var tDay = Today()
Var mDay = MAX(Calendar[Date])

Var t_sDate = DATE(YEAR(tDay)-2;10;1)
Var t_eDate = DATE(YEAR(tDay)-1;9;30)
Var t_dDate = DATE(YEAR(tDay);9;30)
Var t_sDate_2 = DATE(YEAR(tDay)-1;10;1)
Var t_eDate_2 = DATE(YEAR(tDay);9;30)

Var m_sDate = DATE(YEAR(mDay)-2;10;1)
Var m_eDate = DATE(YEAR(mDay)-1;9;30)
Var m_dDate = DATE(YEAR(mDay);9;30)
Var m_sDate_2 = DATE(YEAR(mDay)-1;10;1)
Var m_eDate_2 = DATE(YEAR(mDay);9;30)

Return 

IF(mDay>tDay;

IF(tDay=>t_dDate;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>t_sDate_2;
Calendar[Date]=<t_eDate_2
)

;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>t_sDate;
Calendar[Date]=<t_eDate
)
)

;

IF(mDay=>m_dDate;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>m_sDate_2;
Calendar[Date]=<m_eDate_2
)

;

Calculate(SUM(Sales[Sales]);
ALL(Calendar[Date]);
Calendar[Date]=>m_sDate;
Calendar[Date]=<m_eDate
)
)
)

It's getting a little messy but i hope it's correct! 🙂

View solution in original post

lucasd Regular Visitor
Regular Visitor

Re: Total sales of last year

Perfect! The => were in wrong order and there was a >= where there should be an >. Thanks. Code below:
 
FY-1 =

Var tDay = Today()
Var mDay = MAX('Date'[Date])

Var t_sDate = DATE(YEAR(tDay)-2;10;1)
Var t_eDate = DATE(YEAR(tDay)-1;9;30)
Var t_dDate = DATE(YEAR(tDay);9;30)
Var t_sDate_2 = DATE(YEAR(tDay)-1;10;1)
Var t_eDate_2 = DATE(YEAR(tDay);9;30)

Var m_sDate = DATE(YEAR(mDay)-2;10;1)
Var m_eDate = DATE(YEAR(mDay)-1;9;30)
Var m_dDate = DATE(YEAR(mDay);9;30)
Var m_sDate_2 = DATE(YEAR(mDay)-1;10;1)
Var m_eDate_2 = DATE(YEAR(mDay);9;30)

Return

IF(mDay>tDay;

IF(tDay>t_dDate;

Calculate(SUM(Sales[Sales]);
ALL('Date'[Date]);
'Date'[Date]>=t_sDate_2;
'Date'[Date]<=t_eDate_2
)

;

Calculate(SUM(Sales[Sales]);
ALL('Date'[Date]);
'Date'[Date]>=t_sDate;
'Date'[Date]<=t_eDate
)
)

;

IF(mDay>m_dDate;

Calculate(SUM(Sales[Sales]);
ALL('Date'[Date]);
'Date'[Date]>=m_sDate_2;
'Date'[Date]<=m_eDate_2
)

;

Calculate(SUM(Sales[Sales]);
ALL('Date'[Date]);
'Date'[Date]>=m_sDate;
'Date'[Date]<=m_eDate
)
)
)
 
 
Then 
FY-2 = CALCULATE([FY-1];SAMEPERIODLASTYEAR('Date'[Date]))

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 140 members 1,578 guests
Please welcome our newest community members: