Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |