Forum Discussion
Calculate difference of data from previous available date
Hi,
I’m trying to calculate difference of billed employees from previous available date.
I created measures, from it I’m able to get previous date but not able to get previous value to calculate difference.
Not sure, where I’m making mistakes.
Please help! Thanks in advance.
Can anyone help in creating measure for WTD, MTD, QTD and YTD. I have calendar table created in my model.
The visualization is as below, the red column I’m trying to calculate:
Measure to calculate billed employees:
Billed Emp =
CALCULATE(
COUNT('Data'[Billing Status]),
FILTER('Data', 'Data'[Billing Status]="Billed")
)
Measure to get the previous value:
Daily Billed =
VAR PrevDate =
MAXX(
FILTER(
ALL('Data'[Dump Date]),
'Data'[Dump Date] < SELECTEDVALUE('Data'[Dump Date])
),
'Data'[Dump Date]
)
VAR PreBilled =
CALCULATE(
COUNT('Data'[Billing Status]),
FILTER('Data', 'Data'[Billing Status]="Billed"),
FILTER('Data','Data'[Dump Date]= PrevDate)
)
return
PreBilled
just, a bit editing))
below script is work
Daily Billed =
VAR PrevDate =
MAXX(
FILTER(
ALL('Data'[Dump Date]),
'Data'[Dump Date] < max('Data'[Dump Date])
),
'Data'[Dump Date]
)
VAR PreBilled_perivous =
CALCULATE(
[Billed Emp],
'Data'[Dump Date]=PrevDate
)VAR result = [Billed Emp]-PreBilled_perivous
return resultHi, Powerful
Please try the following methods. The calculation starts with Billed Emp as the measure.
PreBilled = Var PrevDate=MAXX(FILTER(ALL('Data'[Date]),'Data'[Date]<SELECTEDVALUE('Data'[Date])),'Data'[Date]) Var PreBilled=CALCULATE([Billed Emp],FILTER(ALL(Data),[Date]=PrevDate)) Return PreBilledDaily Billed = IF([PreBilled]<>BLANK(),[Billed Emp]-[PreBilled],BLANK())Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
v-zhangtiCommunity Support
Hi, Powerful
Please try the following methods. The calculation starts with Billed Emp as the measure.
PreBilled = Var PrevDate=MAXX(FILTER(ALL('Data'[Date]),'Data'[Date]<SELECTEDVALUE('Data'[Date])),'Data'[Date]) Var PreBilled=CALCULATE([Billed Emp],FILTER(ALL(Data),[Date]=PrevDate)) Return PreBilledDaily Billed = IF([PreBilled]<>BLANK(),[Billed Emp]-[PreBilled],BLANK())Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks v-zhangti,
Can anyone help in creating measure for WTD, MTD, QTD and YTD. I have calendar table created in my model.
Thanks.
- SolomonovAnton
Super User
try it
Daily Billed =
VAR PrevDate =
MAXX(
FILTER(
ALL('Data'[Dump Date]),
'Data'[Dump Date] < max('Data'[Dump Date])
),
'Data'[Dump Date]
)
VAR PreBilled_perivous =
CALCULATE(
sum([Billed Emp]),
FILTER('Data', 'Data'[Dump Date]=PrevDate) ]
)VAR result = sum([Billed Emp])-PreBilled_perivous
return resultThanks SolomonovAnton for reply.
sum([Billed Emp]) is not working as Billed Emp is not a column it is a measure.
Please suggest! Thanks a lot!
- SolomonovAnton
try it change to
Daily Billed =
VAR PrevDate =
MAXX(
FILTER(
ALL('Data'[Dump Date]),
'Data'[Dump Date] < max('Data'[Dump Date])
),
'Data'[Dump Date]
)
VAR PreBilled_perivous =
CALCULATE(
[Billed Emp],
FILTER('Data', 'Data'[Dump Date]=PrevDate) ]
)VAR result = [Billed Emp]-PreBilled_perivous
return result
