cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: same Sum or Number by Day for a week ago

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

9 REPLIES 9
Highlighted
Super User IX
Super User IX

Re: same Sum or Number by Day for a week ago

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User IX
Super User IX

Re: same Sum or Number by Day for a week ago

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: same Sum or Number by Day for a week ago

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

Highlighted
Frequent Visitor

Re: same Sum or Number by Day for a week ago

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 

 

 

Highlighted
Helper I
Helper I

Re: same Sum or Number by Day for a week ago

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

Highlighted
Helper I
Helper I

Re: same Sum or Number by Day for a week ago

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 .
Highlighted
Super User IX
Super User IX

Re: same Sum or Number by Day for a week ago

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: same Sum or Number by Day for a week ago

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

Highlighted
Helper I
Helper I

Re: same Sum or Number by Day for a week ago

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
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors