cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fiala
Frequent Visitor

gradual countdown of values

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.

Fiala_0-1626002675528.png

 

2 ACCEPTED SOLUTIONS
v-luwang-msft
Community Support
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:

vluwangmsft_0-1626250888041.png

 

 

You could download my pbix flie if you need!

 

Wish it is helpuful for you!

 

Best Regards

Lucien

View solution in original post

v-luwang-msft
Community Support
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 )
    )

  

vluwangmsft_0-1626313155818.png

 

Best Regards

Lucien

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
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 )
    )

  

vluwangmsft_0-1626313155818.png

 

Best Regards

Lucien

View solution in original post

v-luwang-msft
Community Support
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:

vluwangmsft_0-1626250888041.png

 

 

You could download my pbix flie if you need!

 

Wish it is helpuful for you!

 

Best Regards

Lucien

View solution in original post

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: 

Fiala_0-1626284041290.png

It should be 20 in February and March not 10 in this case :(...

 

Tab:

Fiala_1-1626284267671.png

 

ryan_mayu
Super User II
Super User II

@Fiala 

could you pls proivde the sample data?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors