cancel
Showing results for
Did you mean: Frequent Visitor

Hello, can you help me please with measure?

I want the value from the "value -" column to be gradually subtracted from the value in the "value +" column from the oldest date if the value "value +" is> 0. I need to calculate this to calculate overtime for workers. Thank you for any advice and help. 2 ACCEPTED SOLUTIONS  Community Support

Hi @Fiala ，

Try measure like below:

``````test1 =
VAR SUMALL =
CALCULATE (
SUM ( 'Table'[value +] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Name] = MAX ( 'Table'[Name] )
)
)
VAR SUMLAST =
CALCULATE (
SUM ( 'Table'[value +] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] < MAX ( 'Table'[Date] )
&& 'Table'[Name] = MAX ( 'Table'[Name] )
)
)
VAR MINUSS =
CALCULATE (
SUM ( 'Table'[value -] ),
FILTER ( ALL ( 'Table' ), 'Table'[Name] = MAX ( 'Table'[Name] ) )
)
RETURN
IF (
SUMLAST > MINUSS,
MAX ( 'Table'[value +] ),
IF ( SUMALL > MINUSS, SUMALL - MINUSS, 0 )
)
``````

Final get the below: Wish it is helpuful for you!

Best Regards

Lucien  Community Support

Hi  @Fiala ,

Change the max in dax to sum  when return ,see the below:

``````test2 =
VAR SUMALL =
CALCULATE (
SUM ( 'Table'[value +] ),
FILTER (
ALL ( 'Table' ),
'Table'[mon] <= MAX ( 'Table'[mon] )
&& 'Table'[meno] = MAX ( 'Table'[meno] )
)
)
VAR SUMLAST =
CALCULATE (
SUM ( 'Table'[value +] ),
FILTER (
ALL ( 'Table' ),
'Table'[mon] < MAX ( 'Table'[mon] )
&& 'Table'[meno] = MAX ( 'Table'[meno] )
)
)
VAR MINUSS =
CALCULATE (
SUM ( 'Table'[value -] ),
FILTER ( ALL ( 'Table' ), 'Table'[meno] = MAX ( 'Table'[meno] ) )
)
RETURN
IF (
SUMLAST > MINUSS,
sum ( 'Table'[value +] ),
IF ( SUMALL > MINUSS, SUMALL - MINUSS, 0 )
)`````` Best Regards

Lucien

4 REPLIES 4  Community Support

Hi  @Fiala ,

Change the max in dax to sum  when return ,see the below:

``````test2 =
VAR SUMALL =
CALCULATE (
SUM ( 'Table'[value +] ),
FILTER (
ALL ( 'Table' ),
'Table'[mon] <= MAX ( 'Table'[mon] )
&& 'Table'[meno] = MAX ( 'Table'[meno] )
)
)
VAR SUMLAST =
CALCULATE (
SUM ( 'Table'[value +] ),
FILTER (
ALL ( 'Table' ),
'Table'[mon] < MAX ( 'Table'[mon] )
&& 'Table'[meno] = MAX ( 'Table'[meno] )
)
)
VAR MINUSS =
CALCULATE (
SUM ( 'Table'[value -] ),
FILTER ( ALL ( 'Table' ), 'Table'[meno] = MAX ( 'Table'[meno] ) )
)
RETURN
IF (
SUMLAST > MINUSS,
sum ( 'Table'[value +] ),
IF ( SUMALL > MINUSS, SUMALL - MINUSS, 0 )
)`````` Best Regards

Lucien  Community Support

Hi @Fiala ，

Try measure like below:

``````test1 =
VAR SUMALL =
CALCULATE (
SUM ( 'Table'[value +] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Name] = MAX ( 'Table'[Name] )
)
)
VAR SUMLAST =
CALCULATE (
SUM ( 'Table'[value +] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] < MAX ( 'Table'[Date] )
&& 'Table'[Name] = MAX ( 'Table'[Name] )
)
)
VAR MINUSS =
CALCULATE (
SUM ( 'Table'[value -] ),
FILTER ( ALL ( 'Table' ), 'Table'[Name] = MAX ( 'Table'[Name] ) )
)
RETURN
IF (
SUMLAST > MINUSS,
MAX ( 'Table'[value +] ),
IF ( SUMALL > MINUSS, SUMALL - MINUSS, 0 )
)
``````

Final get the below: Wish it is helpuful for you!

Best Regards

Lucien Frequent Visitor

HI @v-luwang-msft , Thank you for your help. Your solution almost works great, but It doesn´t works if i need to see like this: It should be 20 in February and March not 10 in this case :(...

Tab:   Super User II

could you pls proivde the sample data?

Proud to be a Super User! Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  