Help with calculated column

[Syndicate_Admin]

Power BI Community, Cordial Greeting.

I'm around to ask you for a big help with a calculated column that I want to make.

I have 2 boards, 1. Semanas_Epidemiologica and 2. Report table, in table number 2 of reports I want to add a new column where I get according to the date of enrollment to which week corresponds from the table Semanas_Epidemiologica,as well:

1. Table of Semanas_Epidemiologica Fec_Ini_Sem Fec_Fin_Sem Sem_Epi 29/12/2019 04/01/2020 No 1 05/01/2020 11/01/2020 Without 2 12/01/2020 18/01/2020 No 3 19/01/2020 25/01/2020 No 4 26/01/2020 01/02/2020 No 5 02/02/2020 08/02/2020 No 6 09/02/2020 15/02/2020 No 7 16/02/2020 22/02/2020 No 8 23/02/2020 29/02/2020 No 9 01/03/2020 07/03/2020 No 10 08/03/2020 14/03/2020 No 11

2. Reporting table User Fecha_Inscripcion Sem_Epi 123 03/01/2020 ??? 546 20/01/2020 ??? 134 05/03/2020 ??? 458 06/01/2020 ??? 987 07/02/2020 ??? 254 18/02/2020 ??? 158 29/01/2020 ??? 987 10/01/2020 ???

Expected result reporting table User Fecha_Incripcion Sem_Epi 123 03/01/2020 No 1 546 20/01/2020 No 4 134 05/03/2020 No 10 458 06/01/2020 Without 2 987 07/02/2020 No 6 254 18/02/2020 No 8 158 29/01/2020 No 5 987 10/01/2020 Without 2

I hope I've put everything in place so they understand me and can help me.

Thank you very much in advance.

Walter

[parry2k]

@Syndicate_Admin you should mark the correct post as a solution so that others can take advantage of it rather than marking your own reply as a solution. Just advice.

[Syndicate_Admin]

Excellent, thank you very much friends, I worked perfectly.

They're the best 😃

[Anonymous · Accepted solution]

Don'@Syndicate_Admin,

According to my understanding, you want to get the last [sem_Epi] fromtable1 when [Fecha_Inscripcion]is before [Fec_Fin_Sem], right?

As @Vera_33 suggest, you can use the MINX() and LOOKUPVALUE() functions to do this,

Sem_Epi =
VAR _date = [Fecha_Inscripcion]
VAR _near =
    MINX (
        FILTER (
            'Table of Semanas_Epidemiologica',
            'Table of Semanas_Epidemiologica'[Fec_Fin_Sem] >= _date
        ),
        [Fec_Fin_Sem]
    )
RETURN
    LOOKUPVALUE ( 'Table of Semanas_Epidemiologica'[Sem_Epi], [Fec_Fin_Sem], _near )

Or to make it easier to understand, use the following formula:

Sem_Epi 2 =
VAR _date = [Fecha_Inscripcion]
VAR _near =
    CALCULATE (
        MIN ( 'Table of Semanas_Epidemiologica'[Fec_Fin_Sem] ),
        FILTER (
            'Table of Semanas_Epidemiologica',
            'Table of Semanas_Epidemiologica'[Fec_Fin_Sem] >= _date
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table of Semanas_Epidemiologica'[Sem_Epi] ),
        FILTER (
            'Table of Semanas_Epidemiologica',
            'Table of Semanas_Epidemiologica'[Fec_Fin_Sem] = _near
        )
    )

The final output is shown below:

You could take a look at the pbix file here.

Best regards
Eyelyn Qin
If this post helps, then consider Accept it as the solution to help other members find it faster.

[Vera_33]

Hi @Syndicate_Admin @WolterNet 

You don't have duplicated weeks in Table1, right?

Cal Column =

VAR CurDate = Table2[Fecha_Inscripcion]

VAR MinDate = MINX(FILTER(Table1,Table1[Fec_Fin_Sem]>=CurDate),[Fec_Ini_Sem])

RETURN

LOOKUPVALUE(Table1[Sem_Epi],Table1[Fec_Ini_Sem],MinDate)

[parry2k]

@Syndicate_Admin use the following DAX expression to add a column:

Sem_Epi = 
VAR _t = 
CALCULATETABLE ( 
    Seman,
    FILTER ( 
        Seman, 
        Seman[Fec_Fin_Sem]  >= Reporting[Fecha_Inscripcion]
   ) 
) 
VAR _d = 
CALCULATE ( 
    MIN ( Seman[Sem_Epi] ),
    TOPN ( 1, _t, [Fec_Fin_Sem], ASC )
)
RETURN _d 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡