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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks Maggie! The table above is the original parameters matrix. The idea is to have user to input any date as the "start date", then select "issuer status" and "period end" from any category they want, then the "end date" automatically calculate the date except it falls in weekends or holidays. I don't think the current power BI file allow me to select any "period end" for a date, and what formula should it be to have the model automatically calculate the end date? Thanks!