Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Variables sumx only positives

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
Need Help
  • tex628's avatar
    tex628
    6 years ago

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

    Br,
    J

13 Replies

  • tex628's avatar
    tex628
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        tex628
        Community Champion
        6 years ago

        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