I want to apply the following Excel formula to a column in Power BI. Can you help with conversion?Aşağıdaki Excel formülünü Power BI'daki bir sütuna uygulamak istiyorum. Dönüştürme konusunda yardımcı olabilir misiniz? Spoiler (Highlight to read)orijinal Formül; =EĞER(YIL("Tarih")=YIL(BUGÜN());EĞER(HAFTASAY("Tarih";2)>HAFTASAY(BUGÜN();2);(HAFTASAY("Tarih";2)-HAFTASAY(BUGÜN();2))&". Hafta";"Geçmiş İhtiyaç");EĞER(YIL("Tarih")>YIL(BUGÜN());(YIL("Tarih")-YIL(BUGÜN()))*52+HAFTASAY("Tarih";2)-HAFTASAY(BUGÜN();2)&". Hafta";"Geçmiş İhtiyaç"))trasnlate formule;=IF(YEAR("Date")=YEAR(TODAY());IF(WEEKCOUNT("Date",2)>WEEKCOUNT(TODAY(),2);(WEEKCOUNT("Date",2)-WEEKCOUNT(TODAY();2))&". Week";"Past Need");IF(YEAR("Date")>YEAR(TODAY());(YEAR("Date")-YEAR(TODAY()))*52+WEEKCOUNT("Date";2)-WEEKCOUNT(TODAY ) ();2)&". Week";"Past Need"))orijinal Formül; =EĞER(YIL("Tarih")=YIL(BUGÜN());EĞER(HAFTASAY("Tarih";2)>HAFTASAY(BUGÜN();2);(HAFTASAY("Tarih";2)-HAFTASAY(BUGÜN();2))&". Hafta";"Geçmiş İhtiyaç");EĞER(YIL("Tarih")>YIL(BUGÜN());(YIL("Tarih")-YIL(BUGÜN()))*52+HAFTASAY("Tarih";2)-HAFTASAY(BUGÜN();2)&". Hafta";"Geçmiş İhtiyaç"))trasnlate formule;=IF(YEAR("Date")=YEAR(TODAY());IF(WEEKCOUNT("Date",2)>WEEKCOUNT(TODAY(),2);(WEEKCOUNT("Date",2)-WEEKCOUNT(TODAY();2))&". Week";"Past Need");IF(YEAR("Date")>YEAR(TODAY());(YEAR("Date")-YEAR(TODAY()))*52+WEEKCOUNT("Date";2)-WEEKCOUNT(TODAY ) ();2)&". Week";"Past Need"))

Merhaba, in DAX calculated column: WeekComparison = VAR CurrentYear = YEAR(TODAY())VAR CurrentWeek = WEEKNUM(TODAY(), 2)VAR DateYear = YEAR('TableName'[Date])VAR DateWeek = WEEKNUM('TableName'[Date], 2)RETURN IF(DateYear = CurrentYear,IF(DateWeek > CurrentWeek,(DateWeek - CurrentWeek) & ". Week","Past Need"),IF(DateYear > CurrentYear,((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week","Past Need")) In Power Query: = letCurrentYear = Date.Year(DateTime.LocalNow()),CurrentWeek = Date.WeekOfYear(DateTime.LocalNow(), Day.Monday),DateYear = Date.Year([Date]),DateWeek = Date.WeekOfYear([Date], Day.Monday)inif DateYear = CurrentYear thenif DateWeek > CurrentWeek then Number.ToText(DateWeek - CurrentWeek) & ". Week"else "Past Need"else if DateYear > CurrentYear thenNumber.ToText((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week"else "Past Need"

Taylan_GUMUS

New Member

1 year ago

Solved

excel to power formule convert help?

I want to apply the following Excel formula to a column in Power BI. Can you help with conversion?

Aşağıdaki Excel formülünü Power BI'daki bir sütuna uygulamak istiyorum. Dönüştürme konusunda yardımcı olabilir misiniz?

Spoiler

orijinal Formül;

=EĞER(YIL("Tarih")=YIL(BUGÜN());EĞER(HAFTASAY("Tarih";2)>HAFTASAY(BUGÜN();2);(HAFTASAY("Tarih";2)-HAFTASAY(BUGÜN();2))&". Hafta";"Geçmiş İhtiyaç");EĞER(YIL("Tarih")>YIL(BUGÜN());(YIL("Tarih")-YIL(BUGÜN()))*52+HAFTASAY("Tarih";2)-HAFTASAY(BUGÜN();2)&". Hafta";"Geçmiş İhtiyaç"))

trasnlate formule;
=IF(YEAR("Date")=YEAR(TODAY());IF(WEEKCOUNT("Date",2)>WEEKCOUNT(TODAY(),2);(WEEKCOUNT("Date",2)-WEEKCOUNT(TODAY();2))&". Week";"Past Need");IF(YEAR("Date")>YEAR(TODAY());(YEAR("Date")-YEAR(TODAY()))*52+WEEKCOUNT("Date";2)-WEEKCOUNT(TODAY ) ();2)&". Week";"Past Need"))

orijinal Formül; =EĞER(YIL("Tarih")=YIL(BUGÜN());EĞER(HAFTASAY("Tarih";2)>HAFTASAY(BUGÜN();2);(HAFTASAY("Tarih";2)-HAFTASAY(BUGÜN();2))&". Hafta";"Geçmiş İhtiyaç");EĞER(YIL("Tarih")>YIL(BUGÜN());(YIL("Tarih")-YIL(BUGÜN()))*52+HAFTASAY("Tarih";2)-HAFTASAY(BUGÜN();2)&". Hafta";"Geçmiş İhtiyaç"))trasnlate formule;=IF(YEAR("Date")=YEAR(TODAY());IF(WEEKCOUNT("Date",2)>WEEKCOUNT(TODAY(),2);(WEEKCOUNT("Date",2)-WEEKCOUNT(TODAY();2))&". Week";"Past Need");IF(YEAR("Date")>YEAR(TODAY());(YEAR("Date")-YEAR(TODAY()))*52+WEEKCOUNT("Date";2)-WEEKCOUNT(TODAY ) ();2)&". Week";"Past Need"))

General Comment

olgad
1 year ago
Merhaba,

in DAX calculated column:

WeekComparison =
VAR CurrentYear = YEAR(TODAY())
VAR CurrentWeek = WEEKNUM(TODAY(), 2)
VAR DateYear = YEAR('TableName'[Date])
VAR DateWeek = WEEKNUM('TableName'[Date], 2)
RETURN
IF(
DateYear = CurrentYear,
IF(
DateWeek > CurrentWeek,
(DateWeek - CurrentWeek) & ". Week",
"Past Need"
),
IF(
DateYear > CurrentYear,
((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week",
"Past Need"
)
)

In Power Query:

= let
CurrentYear = Date.Year(DateTime.LocalNow()),
CurrentWeek = Date.WeekOfYear(DateTime.LocalNow(), Day.Monday),
DateYear = Date.Year([Date]),
DateWeek = Date.WeekOfYear([Date], Day.Monday)
in
if DateYear = CurrentYear then
if DateWeek > CurrentWeek then
Number.ToText(DateWeek - CurrentWeek) & ". Week"
else
"Past Need"
else if DateYear > CurrentYear then
Number.ToText((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week"
else
"Past Need"

Kedar_Pande

1 year ago

Taylan_GUMUS

Create a Calculated Column

Custom Column = 
VAR CurrentYear = YEAR(TODAY())
VAR DateYear = YEAR('Table'[Date])
VAR CurrentWeek = WEEKNUM(TODAY(), 2)
VAR DateWeek = WEEKNUM('Table'[Date], 2)

RETURN 
IF(
DateYear = CurrentYear,
IF(
DateWeek > CurrentWeek,
(DateWeek - CurrentWeek) & ". Week",
"Past Need"
),
IF(
DateYear > CurrentYear,
((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week",
"Past Need"
)
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Anonymous

1 year ago

Hi, Taylan_GUMUS

I create a sample table:

Then create a calculated column, try the following dax:

Test = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentWeek = WEEKNUM(TODAY(), 2)
VAR DateYear = YEAR('Table'[Date])
VAR DateWeek = WEEKNUM('Table'[Date], 2)
RETURN
IF(
    DateYear = CurrentYear,
    IF(
        DateWeek > CurrentWeek,
        (DateWeek - CurrentWeek) & ". Week",
        "Past Need"
    ),
    IF(
        DateYear > CurrentYear,
        ((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week",
        "Past Need"
    )
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

olgad
Resident Rockstar
1 year ago
Merhaba,

in DAX calculated column:

WeekComparison =
VAR CurrentYear = YEAR(TODAY())
VAR CurrentWeek = WEEKNUM(TODAY(), 2)
VAR DateYear = YEAR('TableName'[Date])
VAR DateWeek = WEEKNUM('TableName'[Date], 2)
RETURN
IF(
DateYear = CurrentYear,
IF(
DateWeek > CurrentWeek,
(DateWeek - CurrentWeek) & ". Week",
"Past Need"
),
IF(
DateYear > CurrentYear,
((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week",
"Past Need"
)
)

In Power Query:

= let
CurrentYear = Date.Year(DateTime.LocalNow()),
CurrentWeek = Date.WeekOfYear(DateTime.LocalNow(), Day.Monday),
DateYear = Date.Year([Date]),
DateWeek = Date.WeekOfYear([Date], Day.Monday)
in
if DateYear = CurrentYear then
if DateWeek > CurrentWeek then
Number.ToText(DateWeek - CurrentWeek) & ". Week"
else
"Past Need"
else if DateYear > CurrentYear then
Number.ToText((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week"
else
"Past Need"

Kedar_Pande

Super User

1 year ago

Taylan_GUMUS

Create a Calculated Column

Custom Column = 
VAR CurrentYear = YEAR(TODAY())
VAR DateYear = YEAR('Table'[Date])
VAR CurrentWeek = WEEKNUM(TODAY(), 2)
VAR DateWeek = WEEKNUM('Table'[Date], 2)

RETURN 
IF(
DateYear = CurrentYear,
IF(
DateWeek > CurrentWeek,
(DateWeek - CurrentWeek) & ". Week",
"Past Need"
),
IF(
DateYear > CurrentYear,
((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week",
"Past Need"
)
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Anonymous

Not applicable

1 year ago

Hi, Taylan_GUMUS

I create a sample table:

Then create a calculated column, try the following dax:

Test = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentWeek = WEEKNUM(TODAY(), 2)
VAR DateYear = YEAR('Table'[Date])
VAR DateWeek = WEEKNUM('Table'[Date], 2)
RETURN
IF(
    DateYear = CurrentYear,
    IF(
        DateWeek > CurrentWeek,
        (DateWeek - CurrentWeek) & ". Week",
        "Past Need"
    ),
    IF(
        DateYear > CurrentYear,
        ((DateYear - CurrentYear) * 52 + DateWeek - CurrentWeek) & ". Week",
        "Past Need"
    )
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Taylan_GUMUS

New Member

1 year ago

Thank you very much for your help. My job is solved. Super.

Forum Discussion

excel to power formule convert help?

4 Replies

Related content

Need help regarding fine measure formulation

Convert Excel columns into rows in a PBI table

need help formule in powerBI

need help with a formule

Problème Formule DAX