Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

  • 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.

  • Anonymous's avatar
    Anonymous
    Not 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.