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

## Total sales of last year

Dear community,

I'm working on a finance report and I need a few calculations: 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

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

9 REPLIES 9
bhpage Member

## Re: Total sales of last year

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

Thanks,

Ben 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 ) )` lucasd Regular Visitor

## Re: Total sales of last year lucasd 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

## 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: Then I'm able to filter results based on the year the user selects: I would like to have all that in a simple argument with VAR but I can't figure out how.

tex628 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

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

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

Announcements #### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. #### Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones. #### 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

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 140 members 1,578 guests
Recent signins:
• v-jayw-msft • DirectorFury • slessmann • V-pazhen-msft • mdp1202 • jakeryan56 • Tomasjhussey • 8Jael8 • sarah-ortega • mholberg • albank • SquidAssistant • FaridRodriguez • matheusgratz 