Forum Discussion
Need to convert VBA coding into usable Power BI programming language
Hi, I need to conver the following VBA coding into something I can apply to my Power BI data source. I want to compare a date to rows that have other dates or might even be blank rows. The main problem is I do not know how to interpret the VBA language. Any translation of that would also be helpful Thank you.
Function PluckUnreviewed(from_date As Date, to_date As Date, data) As Variant
Debug.Assert (VarType(data) = vbArray + vbVariant)
Debug.Assert (to_date >= from_date)
Dim analysis_size As Long: analysis_size = to_date - from_date
Dim reviewed As Boolean
Dim varResult As Variant
ReDim varResult(analysis_size, 6)
'TODO: find automatic way of determining data height
For i = 1 To 5088
If IsError(data(i, 2)) Then
GoTo IncrementI
End If
'TODO: there has got to be a more effecient way of knowing if analysis date is within range of date being judged
For j = 0 To analysis_size
If IsError(data(i, 3)) Then
GoTo IncrementJ
End If
reviewed = Not (IsEmpty(data(i, 3))) And (from_date + j) >= data(i, 3)
If (IsEmpty(data(i, 2)) And Not (reviewed)) Then
'TODO: clean up
If (data(i, 1)) Then
varResult(j, 5) = varResult(j, 5) + 1
Else
varResult(j, 6) = varResult(j, 6) + 1
End If
ElseIf (from_date + j) >= data(i, 2) Then
If (reviewed) Then
If ((from_date + j) - data(i, 3)) <= 7 Then
varResult(j, 0) = varResult(j, 0) + 1
End If
Else
Select Case ((from_date + j) - data(i, 2))
Case Is <= 7:
varResult(j, 1) = varResult(j, 1) + 1
Case Is <= 30:
varResult(j, 2) = varResult(j, 2) + 1
Case Is <= 60:
varResult(j, 3) = varResult(j, 3) + 1
Case Is > 60:
varResult(j, 4) = varResult(j, 4) + 1
End Select
End If
End If
IncrementJ:
Next j
IncrementI:
Next i
PluckUnreviewed = varResult
End Function
2 Replies
- lbendlinSuper User
Can you formulate the underlying business problem? Trying to "convert from system xxx to Power BI" is usually not very productive (it is called "fighting the API"). Instead, formulate the problem you want to solve, and provide sample data.
- AnonymousNot applicable
Hi Anonymous ,
Could you explain more about your expected output?
It looks a bit like "for and while loops", if that fits the scenario, you can check the link below:
For and While Loops in DAX - Microsoft Power BI Community
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.