Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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:
You could download my pbix flie if you need!
Wish it is helpuful for you!
Best Regards
Lucien
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
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
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:
You could download my pbix flie if you need!
Wish it is helpuful for you!
Best Regards
Lucien
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:
could you pls proivde the sample data?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |