Depending on conditions show values prom previous or new period

[Anonymous]

Hi Everyone,

It's my first time here and I really hope someone will be able to help me. Below there is a challenge I am facing.

Currnetly I have a Baseline which is an average of complaints count in the period of 12 months (this can be changed but does not matter in this case) What is more, Baseline might be recalculated every 1, 2, 3, 4, 6 or 12 months, which means e.g. for Reset value = 2, the Baseline is calculated in Jan, March, May, July, Sept and Nov.

The requirement is to check what was the value in the previous period and depending on the conditions show Baseline from the previous period or the one form 'current' period. To simplyfy let's say I want to show recalculated Baseline only if it was smaller in the new period than in the previous one.

I was able to find a solution for one year period however when I want to see data for two years period I am facing some issues.

Currently, the logic for the Baseline is following (Reset value = 4):

var hasvalue = HASONEVALUE('Local Complaint Created Date'[4M])
return

 if(hasvalue,IF(VALUES('Local Complaint Created Date'[4M])=1,CALCULATE([BaselineRolling],ALLEXCEPT('Local Complaint Created Date','Local Complaint Created Date'[4M]),'Local Complaint Created Date'[Month Number]=1,VALUES('Local Complaint Created Date'[Year Number])),
     IF(VALUES('Local Complaint Created Date'[4M])=2,CALCULATE([BaselineRolling],ALLEXCEPT('Local Complaint Created Date','Local Complaint Created Date'[4M]),'Local Complaint Created Date'[Month Number]=5,VALUES('Local Complaint Created Date'[Year Number]))
         ,IF(VALUES('Local Complaint Created Date'[4M])=3,CALCULATE([BaselineRolling],ALLEXCEPT('Local Complaint Created Date','Local Complaint Created Date'[4M]),'Local Complaint Created Date'[Month Number]=9,VALUES('Local Complaint Created Date'[Year Number])))) ))

Where 

'Local Complaint Created Date'[4M])  = if('Local Complaint Created Date'[Month Number] <=4,1,
if('Local Complaint Created Date'[Month Number] <=8,2,
if('Local Complaint Created Date'[Month Number] <=12,3)))

--------------------------------------------------------------

So, I created my own dataset and did some exercise.

I adjusted the above formula and now it looks like this:  

var avg1 = calculate(AVERAGE(Table1[local complaints]), ALLexcept(Table1, Table1[period]), Table1[period] = 1, VALUES(Table1[Year]))

var avg2 = calculate(AVERAGE(Table1[local complaints]), ALLexcept(Table1, Table1[period]), Table1[period] = 2, VALUES(Table1[Year]))

var avg3 = calculate(AVERAGE(Table1[local complaints]), ALLexcept(Table1, Table1[period]), Table1[period] = 3, VALUES(Table1[Year]))

return

switch(MAX(Table1[period]),

1, avg1,

2, if(avg1<avg2,avg1,avg2),

3, if (avg1<avg2,avg1, if(avg2<avg3,avg2,avg3)))

So the expected result is presented as Required Baseline

Date            |  Current Baseline    | Required Baseline

Jan 2018     |  38.10                      | 38.10

Feb 2018     | 38.10                       | 38.10

Mar 2018    | 38.10                       | 38.10

Apr 2018     | 38.10                       | 38.10

May 2018    | 29.40                       | 29.40

Jun 2018     | 29.40                       | 29.40

Jul 2018      | 29.40                       | 29.40

Aug 2018    | 29.40                       | 29.40

Sept 2018   | 136.10                     | 29.40

Oct 2018     | 136.10                    | 29.40

Nov 2018    | 136.10                    | 29.40

Dec 2018     |136.10                     | 29.40

Jan 2019     |  40.10                      | 29.40

Feb 2019     | 40.10                       | 29.40

Mar 2019    | 40.10                       | 29.40

Apr 2019     | 40.10                       | 29.40

May 2019    | 42.40                       | 29.40

Jun 2019     | 42.40                       | 29.40

Jul 2019      | 42.40                        | 29.40

Aug 2019    | 42.40                        | 29.40

Sept 2019   | 19.10                        |19.10

Oct 2019     | 19.10                       | 19.10

Nov 2019    | 19.10                       | 19.10

Dec 2019     | 19.10                      | 19.10

I hope I make sense, I wanted to post some pictures but didn't know how to add them.

[Anonymous]

Hi there.

Please, before you start doing anything with DAX and Power BI, watch at least this to know how to *correctly* model data:

https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

If your model is correct, it'll be much easier. A hint is that you should never have one big table in the model. Instead, you should always create a star/flake schema. There are many reasons behind this but here's one very good reason:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

The last paragraph says it all:

"The golden rule of data modeling is always the same: always use star schemas. If a column has to be used to slice and dice, then it needs to belong to a dimension. Numbers to aggregate, on the other hand, are stored in fact tables. Tabular lets a developer deviate from the regular star schema architecture. This does not mean that doing it is always a good idea. It seldom is."

I'll have a look at your problem as well and come back if I find something by way of "a solution."

Best
D

[Anonymous]

Thank you darlove for the hint 🙂

Of course I have a star schema implemented, however the model is really big and it would be a challange for me to expalin it here. I just wanted to give here a draft. It is a really complex solution and just would like to see an aproach I should take but later on to implemnt it to my model I will need to modify it anyway.

[Anonymous]

Do you want this to work only on the month level?

Best
D

[Anonymous]

Yes, it should work on month level.

[Anonymous]

Have a good look at this file and analyze its data thoroughly. It might be very close to what you're looking for.

https://1drv.ms/u/s!ApyQEauTSLtOgYMwK0X6PSQUlGZ7zA?e=NKu4Dz

Best
D

[Greg_Deckler]

Any chance you can post sample source data as text? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thank you, Greg! 🙂

This is my data set: 

Column1   | local complaints

1/1/2018	10
1/11/2018	20
1/14/2018	11
2/10/2018	12
3/15/2018	100
3/17/2018	15
4/17/2018	45
5/23/2018	32
6/23/2018	22
7/17/2018	42
7/18/2018	11
8/30/2018	12
9/21/2018	12
10/22/2018	33
11/12/2018	445
12/14/2018	55
1/1/2019	10
1/11/2019	20
1/14/2019	115
2/10/2019	33
3/15/2019	65
3/17/2019	33
4/17/2019	6
5/21/2019	4
6/15/2019	66
7/17/2019	35
7/17/2019	55
8/12/2019	44
9/15/2019	32
10/21/2019	21
11/4/2019	233
12/9/2019	32

Then I add calculeted columns:

Month = MONTH(Table1[Column1])

period = if(Table1[Month] <= 4 ,1, if(Table1[Month] <= 8 ,2,3))

Year = YEAR(Table1[Column1])

[Madelein]

So if I understand correctly your measure for Baseline is the average of local complaints ignoring all reset values i.e 

Baseline = AVERAGE(Table1[local complaints])

[Anonymous]

This is calculation for Reset value = 4, so the Baseline is recalculated every 4 months

and I have 3 different averages: Jan - Apr, May - Aug, Sep - Dec.

[Madelein]

Do you average the averages or sum the averages?

[Anonymous]

In my second post there is a list of row data -  it is number of complaints per specific day.

Then I calculate an avarage number of complaints in each period - there are three:

1st preriod - Januray - April

2nd period - May - August

3rd period - Seprtember - December

It is just an avarage 🙂