Calculate previousday total

[amaleranda]

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

[Anonymous]

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]

Hi @Anonymous ,

Lesson learned. 🙂 thanks for the reply. 

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

Date	Total	PreviousdayTotal
25-09-19	5000
26-09-19	6000	5000
27-09-19	0	6000
28-09-19	9000	0
29-09-19	2500	9000
30-09-19	0	2500

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

Thanks for your time

[Ashish_Mathur]

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.

[amaleranda]

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.

[Ashish_Mathur]

Hi,

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

[amaleranda]

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.