Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ahmed_MEJRI
Helper I
Helper I

same Sum or Number by Day for a week ago

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)

ahmed_MEJRI_0-1596627172710.png

Best Regards,

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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) ))

View solution in original post

9 REPLIES 9
bi_analysis
Frequent Visitor

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))
 

bi_analysis_0-1597055254318.png

Any question,let me know. Nice week ahead.

@ahmed_MEJRI 

 

 

Greg_Deckler
Super User
Super User

@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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 = 

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 .

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.