Convert excel formula into Power bi

[Anonymous]

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 end	Q2
Issuer status	SBC/EGC
Timeline	134
Last day to file	10/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!

[v-juanli-msft · Accepted solution]

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]

Hi Maggie, 

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

Issuer status	Q1	Q2	Q3	Annual
Large Accelerated Filer (LAF)	129	129	60	129
Accelerated Filer (AF)	129	129	75	129
Non Accelerated Filer (NAF)	134	134	90	134
SBC/EGC	134	134	90	134
Initial filer	134	134	45	134
Loss corp/Deliquent filer (LAF/AF)	134	134	45	134

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]

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]

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!