Variables sumx only positives

[Anonymous]

Hi lads

Im struggling with this sumx 

i need it to return only positive numbers and do not calculate negatives

is there any way?

Overtid =

var __dailyReduction = 0,5

var __dailyThreshold = 9,5

var __legalWorktimeReduction = 7,5

var __lastVisibleDate = LASTDATE(Kalender[Date])

var __lastVisibleDate12MBack =

   dateadd( __lastVisibleDate; -0;DAY)

var __12Mperiod =

   DATESBETWEEN(

       Kalender[Date];

       __lastVisibleDate12MBack -0;

       __lastVisibleDate

   )

var __result =

   SUMX(

       __12Mperiod;

       var __baseHours = [timer tot]

       var __shouldApplyReduction =

           __baseHours > __dailyThreshold

       var __reduction =

           __dailyReduction * __shouldApplyReduction

       return

           __baseHours - __reduction

   ) - __legalWorktimeReduction

return

   __result

[tex628]

Hi @Anonymous ,

Give this a try,

Overtid = 
var __dailyReduction = 0,5
var __dailyThreshold = 9,5
var __legalWorktimeReduction = 7,5
var __lastVisibleDate = LASTDATE(Kalender[Date])
var __lastVisibleDate12MBack = dateadd( __lastVisibleDate; -0;DAY)
var __12Mperiod =
   DATESBETWEEN(
       Kalender[Date];
       __lastVisibleDate12MBack -0;
       __lastVisibleDate
   )
var __result =
   SUMX(
       __12Mperiod;
       var __baseHours = [timer tot]
       var __shouldApplyReduction = __baseHours > __dailyThreshold
       var __reduction = __dailyReduction * __shouldApplyReduction
       return
           __baseHours - __reduction
   ) - __legalWorktimeReduction
var __result2 =
SUMX(
    FILTER(
            SUMMARIZE(
                    __12Mperiod;
                    "Hours";
                    var __baseHours = [timer tot]
                    var __shouldApplyReduction = __baseHours > __dailyThreshold
                    var __reduction = __dailyReduction * __shouldApplyReduction
                    return
                __baseHours - __reduction
                );
            [Hours] >= 0 
        ) ; [Hours]
    ) - __legalWorktimeReduction
return
__result2

Let me know how it goes! 

Br,

J

[Anonymous]

No change with this one am i afraid.

I may inform that im using this to determin overtime in a company.

Where as - 7.5 is the amount needed to activate overtime. The hours i need i calculate is the overtime hours without the negative hours impacting the total.

[tex628]

@Anonymous , the more i look at this the more confused i get... 

Overtid =

var __dailyReduction = 0,5

var __dailyThreshold = 9,5

var __legalWorktimeReduction = 7,5

var __lastVisibleDate = LASTDATE(Kalender[Date])

var __lastVisibleDate12MBack =

   dateadd( __lastVisibleDate; -0;DAY)

var __12Mperiod =

   DATESBETWEEN(

       Kalender[Date];

       __lastVisibleDate12MBack -0;

       __lastVisibleDate

   )
- You dateadd -0 days, this should always be a datesbetween the same dates?

var __result =

   SUMX(

       __12Mperiod;

       var __baseHours = [timer tot]

       var __shouldApplyReduction =

           __baseHours > __dailyThreshold

       var __reduction =

           __dailyReduction * __shouldApplyReduction

       return

           __baseHours - __reduction
           - __reduction will always be 0 if __baseHours is 9,5 or below, meaning that there will only be positive values present in the sumx?

   ) - __legalWorktimeReduction

return

   __result

Are you displaying this in a table with individual dates? 

Br,
J

[Anonymous]

Yes it is a date period over 2 weeks. i will post a picture.

the datesbetween are set in different parameters as i have multiple pages on this report.

this actual one was to show summarized daily over dates like in the picture and the datesbetween was just set to 0

[tex628]

Ahh, give me just a moment!

PS. Be careful if thats actual names and numbers from a customer.

[Anonymous]

Information has so relation to anything other than demoing this. no numbers correspond to actual database

[tex628]

This should blank every cell that has a negative value. 

Overtid = 
var __dailyReduction = 0,5
var __dailyThreshold = 9,5
var __legalWorktimeReduction = 7,5
var __lastVisibleDate = LASTDATE(Kalender[Date])
var __lastVisibleDate12MBack = dateadd( __lastVisibleDate; -0;DAY)
var __12Mperiod =
   DATESBETWEEN(
       Kalender[Date];
       __lastVisibleDate12MBack -0;
       __lastVisibleDate
   )
var __result =
   SUMX(
       __12Mperiod;
       var __baseHours = [timer tot]
       var __shouldApplyReduction = __baseHours > __dailyThreshold
       var __reduction = __dailyReduction * __shouldApplyReduction
       return
           __baseHours - __reduction
   ) - __legalWorktimeReduction
return
IF(__result>=0;__result;BLANK())

But if your aim is to get the total on the right correct you're in trouble. In the current state the measure can't handle anything but calculation on individual dates. 

Br, 
J

[Anonymous]

Great!

Im only starting to learning DAX...

Oh. then i have a problem as that is what i need to get done. or summarize it some other way.

I also have the last challenge to this report. i need to get values from monday-friday only above 7.5 as overtime
i then need to calculate all hours sunday and saturday as overtime and add to the total of monday-friday.

Any suggestions?

[tex628]

Hahahahha! This is difficult, like really difficult. It's going to take me a little while to figure out so i'll take a look at it next week. Give me a bump on monday and make sure i dont forget! 👍

Br,
J

[Anonymous]

bump

[tex628 · Accepted solution]

,Just need to check, is there any chance you could send me a copy of the pbix? 

Br,
J

[tex628]

I'm on it 🙂 

[Anonymous]

Many thanks tex!

ill bump you on monday 😎