Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Convert excel formula into Power bi

Hi all, 

I'm trying to import an excel calculation which includes multiple filters into Power Bi, but I'm having big trouble. The formula is to calculate an end date based on user input date and two filters selections, and also to avoid holidays and weekend if the end result falls in either of these categories.

Date: user input (range between 1/1/2018 - 12/31/2021)

Period end: Q1, Q2, Q3, Annual

Issuer status: Large Accelerated Filer (LAF), Accelerated Filer (AF), Non Accelerated Filer (NAF), SBC/EGC, Initial filer, Loss corp/Deliquent filer (LAF/AF)


I was able to create a simple calculation in excel - "Period end" and "Issuer status" are using drop-down selection list, and timeline is formula driven based on the selection of the above two fields. 

 

Timeline =IFS(AND(OR(B3="Q1",B3="Q2",B3="Annual"),OR(B4="Large Accelerated Filer (LAF)",B4="Accelerated Filer (AF)")),"129",AND(OR(B3="Q1",B3="Q2",B3="Annual"),OR(B4="Non Accelerated Filer (NAF)",B4="SBC/EGC",B4="Initial filer",B4="Loss corp/Deliquent filer (LAF/AF)")),"134",AND(B3="Q3",B4="Large Accelerated Filer (LAF)"),"60",AND(B3="Q3",B4="Accelerated Filer (AF)"),"75",AND(B3="Q3",OR(B4="Initial Filer",B4="Loss Corp/Deliquent filer (LAF/AF)")),"45",AND(B3="Q3",OR(B4="Non Accelerated Filer (NAF)",B4="SBC/EGC")),"90")

 

Date 4/1/19
Period endQ2
Issuer statusSBC/EGC
Timeline134
Last day to file10/9/2019

 

In Power BI, is there a way to create a dashboard with field "last day to file" when having user input of "Date", "Period end" and "Issuer status"? 

 

Thanks very much!

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create measures

    Measure =
VAR M1 =
    MAX ( 'date'[Period end] )
VAR M2 =
    MAX ( [issuer status] )
RETURN
    IF (
        M1 <> "Q3",
        IF (
            M2
                IN {
                "LAF",
                "AF"
            },
            129,
            134
        ),
        SWITCH (
            TRUE (),
            M2 = "LAF", 60,
            M2 = "AF", 75,
            M2
                IN {
                "Initial filter",
                "LAF/AF"
            }, 45,
            M2
                IN {
                "NAF",
                "SBC/EGC"
            }, 90
        )
    )

    Measure 2 = IF(MAX('date'[Date])=MAX('Table'[date]),1,0)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Create measures

    Measure =
VAR M1 =
    MAX ( 'date'[Period end] )
VAR M2 =
    MAX ( [issuer status] )
RETURN
    IF (
        M1 <> "Q3",
        IF (
            M2
                IN {
                "LAF",
                "AF"
            },
            129,
            134
        ),
        SWITCH (
            TRUE (),
            M2 = "LAF", 60,
            M2 = "AF", 75,
            M2
                IN {
                "Initial filter",
                "LAF/AF"
            }, 45,
            M2
                IN {
                "NAF",
                "SBC/EGC"
            }, 90
        )
    )

    Measure 2 = IF(MAX('date'[Date])=MAX('Table'[date]),1,0)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Maggie, 

       

      Thank you very much for your help! I apologize I should have been more clear on the parameters:

      Issuer statusQ1Q2Q3Annual
      Large Accelerated Filer (LAF)12912960129
      Accelerated Filer (AF)12912975129
      Non Accelerated Filer (NAF)13413490134
      SBC/EGC13413490134
      Initial filer13413445134
      Loss corp/Deliquent filer (LAF/AF)13413445134

      The quarters are not necessarily based on the dates but should be selected by the user as the quarters in this context is a fiscal year term not based on calendar. The end result I'm trying to achive is the date after the duration of timeline based on user input of start date, quarter, issuer and at the same time if it falls into a holiday or a weekend, then find the next business day. 

       

      I'm able to list out all combinations in power query, but I'm really interested if there is a way to use measures and calculations to achieve the same results.  

       

      Thank you so much!

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi Anonymous 

        The table above is your original table, right?

        Your expected result is the date, Right?

        for example, when select "Q1", for "Large Accelerated Filer (LAF)", the desired date should be the last date of Q1 +129(except the holiday and weekends). 

         

        Best Regards

        Maggie