cancel
Showing results for
Search instead for
Did you mean:
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
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
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 ) )`

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.

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.

I appreciate your help. Thanks

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

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

Highlighted
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

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)