Pro request measure does not show values

Carteam 20-03-2023 WIP test dates.pbix (54.2 MB)

The measure Vrijval RT does not give me any values.

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(
RELATEDTABLE(‘DateTable’),
‘DateTable’[YearMonth]),
“Multiplier”,IF(DATEDIFF(maxdate,[YearMonth],Month)=0,0.5,
IF( DATEDIFF(maxdate,[YearMonth],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)))
),([Voorschot]/12)*[Multiplier]
),
periodintable)

RETURN output

I tried to see what went wrong so I used Calculatetable instead of calculate

Back tracked my steps and periodintable variable needs a date instead of yearmonth. So changed this to ‘DateTable’[Date].
It now gives me a value but completely the wrong value.
checked it in the virtual table (Calculatetable) but it does not show any values in that table.

anyone any ideas?

Bumping this post for more visibility from our experts and users.

Strongly hope someone can help me with this.

Hi @Diabolica - I have gone through the code. Can you please explain what output are you expecting and what is the logic required for any further help.

Thanks
Ankit J

1 Like

Hi @Diabolica,

We’ve noticed that no response was received from you on the post above.

Just following up if you still require assistance to solve your inquiry.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

To start, thank you very much for your help.

for explaining the model : Reporting date is 2022 06

Count subscribtion
Jan signups 105
Feb signups 110
Mar signups 120
Apr signups 130
May signups 80
Jun signups 100

EC value ( value of subscription one year)
Jan - Mar 28,-
Apr - Jun 30,-

Now I need to spread the taken revenue over the respected months.

Spread over 12 months but first is 0,5 and 13th month is also addind 0,5 a months value

1st month * 0,5
2nd month * 1,5
3th month * 2,5

Jan 105 * 28,- * 0,5
Feb 110 * 28,- * 1,5
etc

This needs to summed up in the Vrijval RT Measure

Hi @Diabolica

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts’ and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

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

This shows that datediff 1 month 2 month and 3 month all give 11.5 as a result.

One month shows 0.5 as expected

Hi @Diabolica

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts’ and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!