previous year to date formula

[stewwe]

Hello,

i have the following problem right now:

I want to calculate the „previous year to date“ for our sales data
In the end my table should look like this =
Previous January
Previous February
Previous March
Previous April till today

Thank you for your help!

Stewwe

 

[Noodleski]

ok, but it would help to know what kind of data is available in your source data. Is it yearmonth, a date, year and month separately in a column,... Rather than explaining, it usually is a good idea to provide a dozen lines of example data. (remove all sensitive data, if applicable)

 

[stewwe]

Hello Noodleseki,

thank für your hint

The sales data is stored in a fact table. The fact table is connected to a typical Dimension calendar table as you can see:

Facts:
ID Sales Datum
1 3 01.01.2016
2 4 05.01.2016
3 7 27.01.2016
4 4 01.02.2016
5 3 02.02.2016
6 8 03.02.2016
7 7 04.02.2016
8 4 05.02.2016
9 6 06.02.2016
10 7 07.02.2016
11 3 08.02.2016
12 6 05.03.2016
13 1 20.03.2016
14 9 04.04.2016
15 6 10.04.2016

Dimension_Date

Date year Month Day
01.01.2016 2016 1 1
02.01.2016 2016 1 2
03.01.2016 2016 1 3
04.01.2016 2016 1 4
05.01.2016 2016 1 5
06.01.2016 2016 1 6
07.01.2016 2016 1 7
08.01.2016 2016 1 8
09.01.2016 2016 1 9
10.01.2016 2016 1 10
11.01.2016 2016 1 11
12.01.2016 2016 1 12
13.01.2016 2016 1 13
14.01.2016 2016 1 14
15.01.2016 2016 1 15
16.01.2016 2016 1 16
17.01.2016 2016 1 17
18.01.2016 2016 1 18

 

[Noodleski]

Hi again,

much better. Now at least we know what we're dealing with. Since it's unlikely that your date format is as it's displayed (with the periods in between month, day and year) this field is probably a text string, not recognized as date.
Personally, I'd add a date field, based on that field and disregard the calendar table entirely.

In column D (assuming your data above starts in A and goes up to C) => this goes into helper column D, in my example
DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2))

Then for the YTD this year (assuming the date you wish to use as filter is in H1)

SUMIFS($B$2:$B$31,$D$2:$D$31,"<"&$H$1,$D$2:$D$31,">="&DATE(YEAR($H$1),1,1))
for Year -1
SUMIFS($B$2:$B$31,$D$2:$D$31,"<"&DATE(YEAR($H$1)-1,MONTH($h$1),DAY($h$1)),$D$2:$D$31,">="&DATE(YEAR($H$1)-1,1,1))

 

[stewwe]

Thank you for your efforts Noodleski!

I thought it was clear, that I need the formular in dax language, because I asked in the powerbi forum. Sorry for that!

The best formular I´ve found so far has been:

=calculate([Sales];DATEADD(DATESYTD(Dim_Date[Date]);-1;year))

but this didn´t solve my problem at all :-\

 

[Noodleski]

Oh... my bad. I didn't notice the subforum. And I don't use DAX so I can't help you, I'm afraid.

Es tut mir Leit, Stewwe.

 

[stewwe]

Kein Problem Noodleski

Anyone else who can help me?