returning true for multiple criteria in calculated column

[Anonymous]

I'm trying to create a calculated column that shows true if the criteria is met. 
The criteria is that if the current day offset matches what I search then it returns true. I'm looking for the the past 4 days that are the same day of the week as today. Using Current day offset allows this to be dynamic. 

I am new so please go easy on me. the below is returning the result "#ERROR"

is past 4 = IF('Date'[CurrDayOffset] =-6, 
    && ('Date'[CurrDayOffset] = -13
    && ('Date'[CurrDayOffset] = -20
    && ('Date'[CurrDayOffset] = -27
    THEN  "True"
    else  "False"
End 

[ToddChitt · Accepted solution]

is past 4 = IF('Date'[CurrDayOffset] =-6
|| 'Date'[CurrDayOffset] = -13
|| 'Date'[CurrDayOffset] = -20
|| 'Date'[CurrDayOffset] = -27
, "True"
, "False"
)

The IF statement in DAX is pretty ubiquitous. Learn it. 

What is the logic you are trying to accomplish here? 

[Anonymous]

@ToddChitt I understand the IF Statement from excel. I've discovered that with some functionalty, the cross over from excel to PBI is not always the same hence why I was clutching at straws trying to manipulate the IF statement to make this work. 

My overall goal is from my table named " Production Stats" use a column named "Daily total Packs Per Hr" to find the the value for the same day as today but for the previous four weeks (not including today) and average this value out. The reason I was looking to use current day offset is becuase I want it to update every day to show the past 4 Mondays, or past 4 Tuesdays. All relevant to the current day of the week. 

30.09.22	355	328	4501
03.10.22	325	234	4241
04.10.22	323	215	4195
05.10.22	313	268	4072
06.10.22	0	0	0
07.10.22	0	0	0
10.10.22	267	417	3604
11.10.22	326	212	4195
12.10.22	319	264	3954
13.10.22	308	247	4281
14.10.22		0	4120
17.10.22	315	459	3783
18.10.22	332	423	4054
19.10.22	323	557	3882
20.10.22	340	1232	4504
21.10.22	357	579	3742
24.10.22	358	608	4073
25.10.22	376	532	4178
26.10.22	364	685	3913
27.10.22	353	537	3908
28.10.22	195	301	3257
31.10.22		324	3430
01.11.22
02.11.22

This final value will be returned on a card. 

I've attached some screenshots and excel data

[v-tangjie-msft]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create  a calculated column.

Column = var _max_date = MAX('Table'[Date])

var _curent_date = 'Table'[Date]

return

if(_curent_date in {_max_date-7,_max_date-14,_max_date-21,_max_date-28},1,0)

(3) We can create  a measure. 

average = var _a = CALCULATE(SUM('Table'[Daily total Packs Per Hr(Line 2)]),FILTER(ALL('Table'),'Table'[Column]=1))
var _b=CALCULATE(SUM('Table'[Column]),FILTER(ALL('Table'),'Table'[Column]=1))
var _c = DIVIDE(_a,_b,blank())
return _c

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Thank you for your detailed response much appreciated. ToddChitt's solution did exactly was I was looking for. 

[ToddChitt]

If you would, please mark the answer as an aAcepted Solution, that way others investigating similar issues can know this has a good answer. Also helps us responders 'level up' :).

[Anonymous]

Hi @ToddChitt both suggestions return a syntax error. I appreciate you taking the time to reply. 

there may be a completely different way to achieve this I'm just to new to power BI to know if there is. 

[ToddChitt]

Sorry, replace END with )

Syntax for IF statement in DAX is:

IF ( <expression to evaluate to either TRUE or FALSE>, <Value to return if TRUE>, <Value to return if FALSE> )

[Anonymous]

@ToddChitt I tried that previous to you replying. It auto corrected the singular ")" to "))))" then returned an error. 

[ToddChitt]

But the above will ALWAYS return FALSE because Date'[CurrDayOffset] cannot be BOTH a value of -6 AND a value of -13 at te same time. Try some OR logic by substituting double pipes for the double &&:

is past 4 = IF('Date'[CurrDayOffset] =-6
|| ('Date'[CurrDayOffset] = -13
|| ('Date'[CurrDayOffset] = -20
|| ('Date'[CurrDayOffset] = -27
, "True"
, "False"
End

[ToddChitt]

is past 4 = IF('Date'[CurrDayOffset] =-6
&& ('Date'[CurrDayOffset] = -13
&& ('Date'[CurrDayOffset] = -20
&& ('Date'[CurrDayOffset] = -27
, "True"
, "False"
End