cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

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

2 ACCEPTED SOLUTIONS
Responsive Resident

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 result

Community 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
PreBilled``````
``````Daily 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 7
Community 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
PreBilled``````
``````Daily 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.

Frequent Visitor

Thanks v-zhangti,

Can anyone help in creating measure for WTD, MTD, QTD and YTD. I have calendar table created in my model.

Thanks.

Responsive Resident

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 result

Frequent Visitor

sum([Billed Emp]) is not working as Billed Emp is not a column it is a measure.

Responsive Resident

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

Frequent Visitor

Thanks @SolomonovAnton

I have tried this.

This is giving the same data as [Billed Emp]. it is taking PreBilled_perivous as Zero hence [Billed Emp]-PreBilled_perivous same as [Billed Emp].

Thanks!!

Responsive Resident

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 result

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.