Total sales of last year

[Anonymous]

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?

[jdbuchanan71]

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

[Anonymous]

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]

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

[tex628]

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

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

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]

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

Thanks,

Ben

[Anonymous]