SUMX & DATEADD give blank result

[Miewmoo_33]

Hi everybody!

I 'm new in DAX and I get stusk with a dax formula I would like to understand and go futher of my understanding of DAX langage🙂:

My data model is quite simple.

I get a rate for each week and need to know if someone has 2 bad rates in a row (i.e someone who gets A bad rate (<0) for each of the 2 last weeks).

I thought about create a Sumx with calculate inside to get the rate of the previous week, than add this mesure with the one of the current week and test if I get a 2 values but the mesure of the previous week's rate guive me just blanks.😥

Mesure for rate of the previous week : 

BadRateS-1 = sumx(

            Tablefact,

calculate(

sum(Tablefact[isBadRate]),

DATEADD(Calendar[Date],

-7,

DAY)

)

)

Student	Rate	Date	IsBadRate (O/1)
A	-1	12/10/2020	1
A	2	03/10/2020	0

Thanks a lot for your help.

Regards. 

[Miewmoo_33]

Thanks @amitchandak ,

Your formula works fine but I have got issue with adding that mesure with the first one in order to calculate the total of the last 2 weeks : 

total_Last2Weeks =
VAR _SumIndiv=
    CALCULATE ( IF ( ( test [IsBadS-1] + [IsBad] ) = 2, "Warning" ) )
RETURN
  _SumIndiv

The level individual agregation and even more the total agregation get wrong.

Thnaks for helphing me again. 

[amitchandak · Accepted solution]

@Miewmoo_33 , Hope date table is marked as date table and have all required dates.

This should work
calculate(
sum(Tablefact[isBadRate]),
DATEADD(Calendar[Date],-7,DAY) )

Refer for week vs week my blogs

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...