Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative values in %

Hello
i wrote some dax query but i'm not getting the right result, can someone help me to take a look at my query please?
here is my query

cumul_test = var _Female = F_Rtr[%Female]
var _JoinersFemale = F_Joiners[% Female Hired]
var _mnt = SELECTEDVALUE(D_Calendar[Month])
var _cal =  _Female +_JoinersFemale

var _cumul_1 =CALCULATE(_JoinersFemale,DATEADD(D_Calendar[Month],+1,MONTH))

var _test_test = _cumul_1 +_cal

RETURN
_test_test


can someone help me to write the correct syntax please. what i'm expected to have as result is for exemple

 

for sept 2020

%Female = 20
_JoinersFemale = 5

_cumul = 25

for Oct 2020

_Cumul + octobre values of _JoinersFemale for exemple 10

so _cumul for Octobre should be 25 + 10 = 35%

 




  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , This should be a date, not a month. other options use allselected in place of all

     

    var _cumul = CALCULATE(_RosterFemale + _FJoinersFemale - _FLeavers,FILTER(allselected (D_Calendar[Month]),
    D_Calendar[Date] <= MAX(D_Calendar[Date])))

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , the expected output is not clear. But cumulative is like

     

    CALCULATE(_JoinersFemale,Filter(all(Date), Date[Date] <= MAX(Date[Date])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hello
    i made this table

    using the precedent formula i'm not getting the right result here is how is calculated for exemple the first ligne
    sept the cumul is 29,12%

    in oct we should have 29,12 - 2,26%(F leavers) +2,8(F hired)
    here is the formula

    cumul = var _RosterFemale = F_r[%Female_1]
    var _FJoinersFemale = F_Joiners[% Female Hired]
    var _FLeavers = F_Leavers[%Female_Levers]

    var _cumulF = _RosterFemale + _FJoinersFemale - _FLeavers

    var _cumul = CALCULATE(_RosterFemale + _FJoinersFemale - _FLeavers,FILTER(ALL(D_Calendar[Month]),
    D_Calendar[Month] <= MAX(D_Calendar[Month])))

    Return


    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      5 years ago

      Anonymous , This should be a date, not a month. other options use allselected in place of all

       

      var _cumul = CALCULATE(_RosterFemale + _FJoinersFemale - _FLeavers,FILTER(allselected (D_Calendar[Month]),
      D_Calendar[Date] <= MAX(D_Calendar[Date])))