Forum Discussion
Help with calculated column
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
| 2. Reporting table
| Expected result reporting table
|
I hope I've put everything in place so they understand me and can help me.
Thank you very much in advance.
Walter
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.
5 Replies
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 _dCheck 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.⚡
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])RETURNLOOKUPVALUE(Table1[Sem_Epi],Table1[Fec_Ini_Sem],MinDate)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.Excellent, thank you very much friends, I worked perfectly.
They're the best 😃
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.
