I have a calculation within a virtual table.
Does this calculate the min date for each summerized row?
Because when I take 1 month I get the value 0.5 month which is correct.
But when i use 2 months period i get a total of 23 months back where I would expect 0.5 + 1.5 = 2.
Test Measure:
Vrijval =
VAR __reportyear = IF(SELECTEDVALUE(‘Rapportage jaar’[Rapportage jaar orders])=0,SELECTEDVALUE(UWY_Jaar[Year]),SELECTEDVALUE(UWY_Jaar[Year])+1)
VAR __mindate = DATE([UWY],1,1)
VAR __max_month = SELECTEDVALUE(‘Rapportage maand’[Rapportage maand])
VAR __maxdate = EOMONTH(DATE(__reportyear,__max_month,1),0)
VAR __periodintable =
filter(
ALL(DateTable),
AND(DateTable[Date] <= __maxdate,
DateTable[Date] >= __mindate))
VAR __output =
CALCULATE(
SUMX(
ADDCOLUMNS(
SUMMARIZE(‘DateTable’,
‘DateTable’[YYYY-MM]),
“Multiplier”,
VAR __MindateX = MIN(DateTable[Date])
RETURN
IF(DATEDIFF(__maxdate,__MindateX,Month)=0,0.5,
IF( DATEDIFF(__maxdate,__MindateX,Month) >= 12 , 12,
SWITCH(
1,1.5,
2,2.5,
3,3.5,
4,4.5,
5,5.5,
6,6.5,
7,7.5,
8,8.5,
9,9.5,
10,10.5,
11,11.5)))
),[Multiplier]
),
__periodintable)
RETURN __output
Real measure with the needed calculations.
Vrijval RT =
VAR __reportyear = IF(SELECTEDVALUE(‘Rapportage jaar’[Rapportage jaar orders])=0,SELECTEDVALUE(UWY_Jaar[Year]),SELECTEDVALUE(UWY_Jaar[Year])+1)
VAR __mindate = DATE([UWY],1,1)
VAR __max_month = SELECTEDVALUE(‘Rapportage maand’[Rapportage maand])
VAR __maxdate = EOMONTH(DATE(__reportyear,__max_month,1),0)
VAR __periodintable =
filter(
ALL(DateTable),
AND(DateTable[Date] <= __maxdate,
DateTable[Date] >= __mindate))
VAR output =
CALCULATE(
SUMX(
ADDCOLUMNS(
SUMMARIZE(‘DateTable’,
‘DateTable’[End of Month]),
“Multiplier”,
VAR __MindateX = MIN(DateTable[Date])
RETURN
IF(DATEDIFF(__maxdate,__MindateX,Month)=0,0.5*[Voorschot]/12,
IF( DATEDIFF(__maxdate,__MindateX,Month) >= 12 , [Voorschot],
SWITCH(
1,1.5*[Voorschot]/12,
2,2.5*[Voorschot]/12,
3,3.5*[Voorschot]/12,
4,4.5*[Voorschot]/12,
5,5.5*[Voorschot]/12,
6,6.5*[Voorschot]/12,
7,7.5*[Voorschot]/12,
8,8.5*[Voorschot]/12,
9,9.5*[Voorschot]/12,
10,10.5*[Voorschot]/12,
11,11.5*[Voorschot]/12)))
),[Multiplier]
),
__periodintable)
RETURN output