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 

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!