Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Powerful
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.

 

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:

Powerful_0-1653983820345.png

 

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

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

If my message helped solve your issue, please mark it as Resolved! If it was helpful, consider giving it a Kudos!

View solution in original post

v-zhangti
Community Support
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())

vzhangti_0-1654243732928.png

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.

 

View solution in original post

7 REPLIES 7
v-zhangti
Community Support
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())

vzhangti_0-1654243732928.png

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
Solution Supplier
Solution Supplier

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

 

 

If my message helped solve your issue, please mark it as Resolved! If it was helpful, consider giving it a Kudos!

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

 

 

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

If my message helped solve your issue, please mark it as Resolved! If it was helpful, consider giving it a Kudos!

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

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

If my message helped solve your issue, please mark it as Resolved! If it was helpful, consider giving it a Kudos!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.