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
Anonymous
Not applicable

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
tex628
Community Champion
Community Champion

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

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

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
Community Champion
Community Champion

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
Anonymous
Not applicable

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
Community Champion
Community Champion

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
Anonymous
Not applicable

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]))
Anonymous
Not applicable

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

 

Thanks,

Ben

Anonymous
Not applicable

Capture.JPG

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.