Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Total days after if statement

Hello!

 

I can't get this measure working.

 

I have a table that is filter by 2 date slicers. One is the year and one is the period (4 weeks)

 

I have a measure that calculates the total sick days between the two dates in the period.  If startingdate is earlier then startdate of period it should use the start date of the periode. Also if end date of sick is blank then end date of period should be used with a max of 28 days.

Below you see my table and as you can see i have the days for each row but i need to measure the total days. In my table it shows 28 and not the total of all the days.

 

 

This is the measure use. 

Aantal_verzuim_dagen 3 =
VAR CurrentDate = MAX(DimDatum[Datum])
VAR MinVerzuimData = MIN(AFAS_verzuim[Begindatum_-tijd_verzuim])
VAR MaxVerzuimData = MAX(AFAS_verzuim[Einddatum_verzuim])
VAR MAXVerzuimEnd = IF(ISBLANK(MaxVerzuimData),CurrentDate,MaxVerzuimData)
VAR PeriodeDatumMin = FIRSTDATE(DimDatum[Datum])
VAR PeriodeDatumMax = LASTDATE(DimDatum[Datum])
VAR MinVerzuimDataEnd = IF(MinVerzuimData < PeriodeDatumMin, PeriodeDatumMin,MinVerzuimData)
VAR PeriodeMin = SELECTEDVALUE(DimDatum[Datum])

VAR ActiveVerzuimEmp =
CALCULATE(
SUMX(AFAS_verzuim,AFAS_verzuim[Aantal_verzuim_dagen]),
ALL(DimDatum),
DimDatum[Datum] <= MAXVerzuimEnd,
ISBLANK(AFAS_verzuim[Einddatum_verzuim])
|| AFAS_verzuim[Einddatum_verzuim] > MinVerzuimDataEnd
)

VAR boven28 =
SWITCH(TRUE(),
ActiveVerzuimEmp > 28, 28,0,
ActiveVerzuimEmp)

Return
IF(ActiveVerzuimEmp > 28, 28,ActiveVerzuimEmp)


How can i get the total amount of "Aantal_verzuim_dagen 3"?

 
Thank you,
 
San San
Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Greg_Deckler 

      Hi Greg, Thank you for the reply. I have checked the posts and tried somne but can't get the right total.

       

      I have changed the end of the measure to this:

      VAR CurrentDate = MAX(DimDatum[Datum])
      VAR MinVerzuimData = MIN(AFAS_verzuim[Begindatum_-tijd_verzuim])
      VAR MaxVerzuimData = MAX(AFAS_verzuim[Einddatum_verzuim])
      VAR MAXVerzuimEnd = IF(ISBLANK(MaxVerzuimData),CurrentDate,MaxVerzuimData)
      VAR PeriodeDatumMin = FIRSTDATE(DimDatum[Datum])
      VAR PeriodeDatumMax = LASTDATE(DimDatum[Datum])
      VAR MinVerzuimDataEnd = IF(MinVerzuimData < PeriodeDatumMin, PeriodeDatumMin,MinVerzuimData)
      VAR PeriodeMin = SELECTEDVALUE(DimDatum[Datum])

      VAR ActiveVerzuimEmp =
      CALCULATE(
      SUMX(AFAS_verzuim,AFAS_verzuim[Aantal_verzuim_dagen]),
      ALL(DimDatum),
      DimDatum[Datum] <= PeriodeDatumMax,
      ISBLANK(AFAS_verzuim[Einddatum_verzuim])
      || AFAS_verzuim[Einddatum_verzuim] > PeriodeDatumMin
      )
      VAR _result =
      IF(ActiveVerzuimEmp > 28, 28, ActiveVerzuimEmp)

      VAR _table = SUMMARIZE(AFAS_verzuim,AFAS_verzuim[Aantal_verzuim_dagen],"aantallen",_result)

      Return
      IF(HASONEVALUE(AFAS_verzuim[Aantal_verzuim_dagen]),_result,SUMX(_table,_result))

       

      The total that i'm looking for is 430. Somehow it stil skips some amounts see table below:

       

       

      Any suggestions to make this work?


      Thanks,

       

      San San

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        Anonymous It's generally easiest to implement as 2 measures. Get the individual row measure working. Then create the "total" measure that essentially checks to see if it is in an individual row and if so, return the "single" measure. Otherwise, you have to use a SUMMARIZE or GROUPBY or SUMMARIZECOLUMNS to summarize the current in-context table exactly as in the visual, use ADDCOLUMNS to add your "single" measure and then do a SUMX across that table to get the total. Hard to be specific without data, etc.