excel to power formule convert help?

[Taylan_GUMUS]

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"))

[olgad · Accepted solution]

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 · Accepted solution]

@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

[Taylan_GUMUS]

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

[Anonymous · Accepted solution]

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.

[Kedar_Pande · Accepted solution]

@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

[olgad · Accepted solution]

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"