cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lucasd Helper I
Helper I

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
Super User II
Super User II

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! 🙂


Connect on LinkedIn

View solution in original post

9 REPLIES 9
bhpage Resolver II
Resolver II

Re: Total sales of last year

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

 

Thanks,

Ben

Super User IV
Super User IV

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 Helper I
Helper I

Re: Total sales of last year

Capture.JPG

lucasd Helper I
Helper I

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 Helper I
Helper I

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

Super User II
Super User II

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

Connect on LinkedIn
lucasd Helper I
Helper I

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

 

Super User II
Super User II

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! 🙂


Connect on LinkedIn

View solution in original post

lucasd Helper I
Helper I

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors