Total days after if statement

[SanSan]

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

[Greg_Deckler]

@SanSan This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[SanSan]

@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]

@SanSan 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.

[SanSan]

Hi @Greg_Deckler ,

I have tried with addcolom but somehow got the same result.

I have added a table with the data I use. Where Verzuimmelding is the key and you got the startdate, enddate and the count of days. If end_date is blank then end date should be max period enddate. The period is between 25-04-2022 and 22-05-2022. If counted days is > 28 then it should count 28 days. (for date format it is DD-MM-YY)

The days should be counted for every verzuimmelding where start_date of verzuimmelding is < then end_date and and end_date of verzuim is > startdate of period and when blank max of period enddate.

Could you help me to make this work?

The measure I used:

VAR PeriodeDatumMin = FIRSTDATE(Date_dim[Datum])

VAR PeriodeDatumMax = LASTDATE(Date_dim[Datum])

VAR ActiveVerzuimEmp =

CALCULATE(

SUMX('Table','Table'[Count_days]),

ALL(Date_dim),

Date_dim[Datum] <= PeriodeDatumMax,

ISBLANK('Table'[End_date])

|| 'Table'[End_date] > PeriodeDatumMin)

VAR _result =

IF(ActiveVerzuimEmp > 28,28,ActiveVerzuimEmp)

VAR _table = SUMMARIZE('Table','Table'[Count_days],"aantallen",_result)

Return

CALCULATE(SUMX(ADDCOLUMNS(_table,"Dagen",_result),[Dagen]))

Verzuimmelding	Start_date		End_date		Count_days
144	21-04-22		26-04-22		6
145	9-09-21				281
146	29-12-21		3-01-22		4
156	26-04-22		6-05-22		9
157	7-06-22		10-06-22		2
158	19-08-21				302
159	28-09-21				262
160	6-10-21				254
190	4-03-22				105
191	7-03-22		29-04-22		53
205	30-03-22		2-04-22		2
206	21-02-22		30-05-22		100
207	1-04-22				77
208	4-04-22		11-04-22		7
212	6-04-22		15-04-22		8
213	6-04-22		9-04-22		2
214	6-04-22		16-04-22		26
215	8-04-22				70
216	11-04-22		16-04-22		4
217	11-04-22		14-04-22		7
225	29-04-22		6-06-22		37
226	2-05-22		11-05-22		8
227	25-04-22		17-05-22		21
228	6-05-22		14-05-22		13
229	8-05-22		12-05-22		3
230	9-05-22		14-05-22		4
231	9-05-22				39
232	9-05-22				39
233	16-05-22		19-05-22		7
234	23-05-22		28-05-22		4
235	23-05-22		26-05-22		2
236	23-05-22		4-06-22		11
237	8-06-22		11-06-22		6
238	8-06-22		10-06-22		8
239	9-06-22				8
240	14-06-22				8
277	28-02-22				123
315	13-04-22		27-04-22		13
319	29-04-22		4-05-22		5
320	29-04-22		10-05-22		10
321	2-05-22		7-05-22		4
322	2-05-22				88
323	2-05-22		9-05-22		7
324	9-05-22		14-05-22		4
325	9-05-22		13-05-22		3
326	17-05-22		20-05-22		2
330	25-05-22				23
331	25-05-22		28-05-22		2
332	30-05-22		11-06-22		11
333	30-05-22				18
334	30-05-22		31-05-22		2
335	31-05-22		4-06-22		7
336	1-06-22		14-06-22		12
337	7-06-22		10-06-22		2
338	7-06-22				10
339	7-06-22		11-06-22		3
340	8-06-22		14-06-22		6
341	10-06-22		14-06-22		5
342	13-06-22				15
343	14-06-22				3
370	1-03-22				108
388	3-05-22		10-05-22		10
389	18-05-22		21-05-22		2
390	25-05-22		30-05-22		4
391	31-05-22				21
392	13-06-22				4
395	18-01-21				515
396	10-05-21		19-05-22		373

Thanks,

San San