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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jakob82
Frequent Visitor

New measure with Daily performance from Balance amount

Hello

 

I need help to create a new measure based on a Balance Amount field. The measure need to calculate the daily perfomance, wich is today balance - previous day balance, except when it is a new year. If it is 1/1 we would like to show the current balance for that day.

 

I have a screenshot from excel showing an example. 

 

Annotation 2023-05-12 100625.jpg

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @jakob82 ,

 

Here are the steps you can follow:

1. Create calculated column.

Column =
var _mindate=
MINX(
    FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))),[Date])
var _current=
SUMX(  FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&'Table'[Date]=EARLIER('Table'[Date])),[Value])
var _last=
SUMX(  FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&'Table'[Date]=EARLIER('Table'[Date])-1),[Value])
return
IF(
    'Table'[Date]=_mindate,'Table'[Value],
    _current - _last)

2. Result:

vyangliumsft_0-1684205120346.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Ok thanks!

 

I thoght I had a solution with following measures: 

Measure for Previous day balance:

 

 

GL Balance Amount Previous Day:=CALCULATE([GL Balance Amount], PREVIOUSDAY('Dim Balance Date'[Balance Date]), DATESYTD('Dim Balance Date'[Balance Date]))

 

 

 

Then a measure based on current day balance - Previous Day balance: 

 

 

GL Balance Amount - Daily Change:=CALCULATE([GL Balance Amount] - [GL Balance Amount Previous Day])

 

 

 

And "GL Balance Amount - Daily Change" shows exactly what i want, EXCEPT when I look at the grand total.

 

The total show wrong total amount.. It is very weird I think. Any Idea what it is?

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors