cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amaleranda Regular Visitor
Regular Visitor

Calculate previousday total

Hi DAX gurus, 

 

I use to below fromula to calculate previous day total(Measure).

 

It does not seem to like me :(.

 

Any help would be really appriciated.

 

PreviousDayTotal :=
VAR DayPrevous = DATEADD(Targets[Date], -1, DAY)
VAR Total = SUM(Targets[Total])
VAR TotalPerviousDay=
CALCULATE(
[Total],
DayPrevous
 
)

RETURN
TotalPerviousDay

 

 

6 REPLIES 6
adetogni Senior Member
Senior Member

Re: Calculate previousday total

maybe instead of "formula does not seem to like me" show expected results and outcome, it would be easier to help you.

Anyway i'm not 100% sure you can create a measure inside a var and recalculate it again in the result.

 

Try to create a measure
TotalSum= SUM(Targets[Total])

Then a new one

PreviousDayTotal =
VAR DayPrevous = DATEADD(Targets[Date], -1, DAY)
VAR TotalPerviousDay=
CALCULATE(
[TotalSum],
DayPrevous
)
RETURN
TotalPerviousDay
 
 
 
amaleranda Regular Visitor
Regular Visitor

Re: Calculate previousday total

Hi @adetogni ,

 

Lesson learned. 🙂 thanks for the reply. 

 

I have done it that way but still not getting the result I want

 

 

DateTotalPreviousdayTotal
25-09-195000 
26-09-1960005000
27-09-1906000
28-09-1990000
29-09-1925009000
30-09-1902500

 

Previous day total is the measure I am trying to calculate.

Thanks for your time

Super User
Super User

Re: Calculate previousday total

Hi,

Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  To your visual, drag the Date field from the Calendar Table.  Write these measures

Total = SUM(Data[Value])

PreviousDayTotal = CALCULATE([Total],PREVIOUSDAY(Calendar[Date]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amaleranda Regular Visitor
Regular Visitor

Re: Calculate previousday total

Hi, @Ashish_Mathur 

 

I have a calander tabel and a relationship with it. 

 

I used it as you have mentioend but seems like it gives me blank values. 

 

does the format of calandar tabel date and the other tabel date has to be the same ? I have a 1* relationship with calander and my fact table.

Super User
Super User

Re: Calculate previousday total

Hi,

Both Tables should just have genuine dates.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amaleranda Regular Visitor
Regular Visitor

Re: Calculate previousday total

Hi @Ashish_Mathur ,

 

thanks a lot for the reply, 

 

I cannot share the data with you as it is related to work.

 

But I believe I was able to sopt the issue based on the suggestion you have. 

 

My date tabel hasn't got a genuine date with year in it. That maybe the reason why. I will let you know ones I figure it out.

 

I will accept your answer as a solution if I am able to fix this based on your feedback. Thanks again.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 376 members 2,912 guests
Please welcome our newest community members: