Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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) ))
hello, try this. i also simulate your situation with 1 fact and 1 dim date above. here is my measure
Any question,let me know. Nice week ahead.
@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])
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
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
Experts, Hello again, @bi_analysis @Greg_Deckler @amitchandak
Note that :
this is my Measure formula nbCalls =
hi, can you give us your pbix sample with few data test, base on that we can go deeply and detect exact situation
@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.
@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) ))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |