same Sum or Number by Day for a week ago

[ahmed_MEJRI]

Hi Experts, I have two tables in my data model :
table called fact and the second called calendar and the relationship between them fact[Date] -- calendar[Date].i have a measure : nbCalls= count (fact[numberCalls])

I tried many formula to calculate the same number of calls in the last week ( it means current day -7) but not worked :

NB_calls_W1 = CALCULATE(fact[NbCalls], FILTER ('Calendar', DATEADD('Calendar'[Date],-7,DAY) ))
--> not worked and the visual shows the same number of the current day :
for example on 22/05/2020 , we should have : nbCalls(S-1) = 125266 (value of 15/04/2020)

NB_calls_W1 = VAR __max = MAX(calendar[Date])
RETURN
calculate(SUMX(fact,fact[nbCalls]) ,filter(all
('Calendar'),Calendar[Date]=__max-7) )
--> Not worked

i have created also this column
DayLASTWeek = DATEADD('Calendar'[Date],-7,DAY)
NB_calls_W1 = CALCULATE((fact[nbCalls]), FILTER ('Calendar', 'Calendar'[Date]= 'Calendar'[DayLastWeek]))
--> Not worked

NB : my calendar Date works successful and i succeed to calculate the same number for previous day with this formula : CALCULATE([nbCalls],PREVIOUSDAY(fact[Date])

(Below find the visual screenshot)

Best Regards,

@Greg_Deckler   @amitchandak   @parry2k   @ruthpozuelo  @az38   @Fowmy 

[bi_analysis]

hello, try this. i also simulate your situation with 1 fact and 1 dim date above. here is my measure 

Last 7 days = CALCULATE(SUM('Fact'[Amt]),FILTER(ALL('date'),'date'[Date DT]=max('date'[Date DT])-7))

Any question,let me know. Nice week ahead.

@ahmed_MEJRI 

[Greg_Deckler]

@ahmed_MEJRI - Should be something along the lines of:

NB_calls_W1
  VAR __Date = MAX(Calendar[Date])
  VAR __DateMin = __Date - 7
  VAR __Table = FILTER(ALL('fact'),'fact'[Date]<=__Date && 'fact'[Date]>=__DateMin)
RETURN
  SUMX(__Table,[nbCalls])

[ahmed_MEJRI]

Hi Experts, i still trying to find the best formula for my problem.

@Greg_Deckler  : i tried your formula : it shows no values 

it tried also

VAR __DT = MAX(calendar[Date])-7
RETURN CALCULATE(fact[NbCalls],FILTER(ALL('fact'),'fact'[Date]=__DT))

--> not worked

@amitchandak : i tried your suggestions and i'm still searching.

i tried also : 

TODAY() - 7
RETURN
CALCULATE (
fact[nbCalls],
FILTER ( ALL (fact), fact[Date] = L7D )
)

--> not worked

i tried also 

DayLastWeek= DATEADD(fact[Date],-7,DAY) (in my fact table and in my calendar table)

CALCULATE(fact[nbCalls], 'Calendar'[DayLastWeek] )

CALCULATE(fact[nbCalls], 'fact'[DayLastWeek] )

--> not worked for both

[ahmed_MEJRI]

Hi @bi_analysis 

I tried
My measure= CALCULATE(fact[nbCalls],FILTER(ALL('Calendar'),'Calendar'[Date]=max('Calendar'[Date])-7))
not worked : shows no values as shown before in screeshot

and I tried

calculeted column : DayLASTWeek = DATEADD(Output[Date],-7,DAY)

My measure= DATEADD(Calendar[Date],-7,DAY)
CALCULATE(fact[nbCalls],FILTER(ALL('Calendar'),'Calendar'[Date]='Calendar'[DayLASTWeek]))
--> not worked : shows no values as shown before in screeshot

[ahmed_MEJRI]

Experts, Hello again,   @bi_analysis  @Greg_Deckler   @amitchandak 

Note that :

this is my Measure formula nbCalls = 

CALCULATE(COUNT(fact[numberCalls]), FILTER (fact, fact[Duration]=0))

my relation : fact[Date] -- Calendar[Date]

so when i calculate this number for the previous day  --> success with this formula :

IF(ISBLANK(

CALCULATE([nbCalls],PREVIOUSDAY(fact[Date]))),0,CALCULATE([nbCalls],PREVIOUSDAY(fact[Date])))

just when i whant to see the same nbCalls for a specific day (Day-7) , i tested many dax formula and it's correct in syntax but shows no values .

[bi_analysis]

hi, can you give us your pbix sample with few data test, base on that we can go deeply and detect exact situation

[Greg_Deckler]

@ahmed_MEJRI - Really need sample data as text and expected result. 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak · Accepted solution]

@ahmed_MEJRI , Try like this without filer

NB_calls_W1 = CALCULATE(fact[NbCalls], DATEADD('Calendar'[Date],-7,DAY) )

or

NB_calls_W1 = CALCULATE(fact[NbCalls], previousday(DATEADD('Calendar'[Date],-7,DAY) ))

with filter
NB_calls_W1 = CALCULATE(fact[NbCalls], FILTER (all('Calendar'), DATEADD('Calendar'[Date],-7,DAY) ))

[ahmed_MEJRI]

Hello Experts, I solved the problem :
the issue was with my formula in the calculated measure in the first step :
nbCalls =
CALCULATE(COUNT(fact[numbercalls]), FILTER (Output, fact[Duration]=0))

when i use it after to calculate the
nbCalls(Day-X) (for example 7 days) it shows no values.

So the solution is to change method
--> change the position of my condition to the final step
First, I create a measure :
CountCalls = COUNT(fact[numbercalls])
CallsWEEK= CALCULATE(fact[CountCalls], DATEADD(fact[Date],-7,DAY))

Finally, NbCalls = CALCULATE(fact[CallsWEEK],fact[Duration]=0)
same thing if we want to have Day-30 or others