Date Feild Conditions

[Boycie92]

Hi

I am hoping someone can help.

I have a data table with a date field. I need the following fields created using DAX or Power Query, but I am struggling with the syntax.

TodaysDate = If Date = Today then yes else no

CurrentWeek = if a date has been created and it falls within the current week commencing (Monday to Friday) the yes else no (The date could be created at any time e.g., the beginning, middle or end of the week).

CurrentMonth = if a date has been created and it falls within the current Month (1st -31st etc) the yes else no (The date could be created at any time e.g., the beginning, middle or end of the Month).

Current6Months = if a date has been created and it falls within the current 6 Month period (April – September etc) the yes else no (The date could be created at any time e.g., the beginning, middle or end of the 6 Months).

Currentyear = if a date has been created and it falls within the current year (April – September etc) the yes else no (The date could be created at any time e.g., the beginning, middle or end of the year).

Thanks

Boycie92

[amitchandak · Accepted solution]

@Boycie92 , refer these new columns

Is Today = if('Date'[Date]=TODAY(),1,0)

This week = if([Date] >= 'today()+-1*WEEKDAY(today() ,2)+1 && [Date] <= today()+ 5-1*WEEKDAY(today(),2) ,1,0)

Month Type = Switch( True(),
Date([Date])= eomonth(Today(),0),1 ,
0
)

las6 6Months Type = Switch( True(),
([Date]) >= eomonth(Today(),-6) && Date([Date])<= eomonth(Today(),0) ,1 ,
0
)

Year Type = Switch( True(),
year([Date])= year(Today()),1,
0
)

Refer : https://www.youtube.com/watch?v=hfn05preQYA

[Boycie92]

Hi @amitchandak @negi007 

Thanks for your help. I am not using a date table currently so I have used @amitchandak suggestions.

for the current 6 months one. I need to define this in terms of Finacial years. So is it needs to be looking from April to september and October to Match. Depending on where the date falls. Any idea how I can do this?

This is also the same for the 12 months. Does the date for between the current fincial year e.g April 2020 - Match 2021. It needs to be dynamic and change based on a new Fianical Year. Any ideas?

Thanks,

Boycie92

[v-robertq-msft · Accepted solution]

Hi, @Boycie92 

According to your follow-up, it seems like you want to create the fields based on the filtered date between April 2020 to Match 2021. I think you can first create a filtered table like this:

Filtered Date=
Filter(‘Date’,[Date]>=Date(2020,4,1)&& [Date]<Date(2021,3,1)

Then you can create the calculated columns based on the DAX formulas posted by amitchandak.

And you can get what you want.

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[Boycie92]

@v-robertq-msft Is there anyway to do this without hardcoding the dates as I need it too change whenever the fiancial year or that 6 month period changes?

[negi007]

attached is the pbix file for your reference

[negi007]

@Boycie92 ok. in this case you will have to create new columns in your date table like below

Today's = IF(Date_Table[Date]=TODAY(),"Yes","No")

Current_Week = if(WEEKNUM(Date_Table[Date],1)=WEEKNUM(TODAY(),1),"Yes", "No")

Current_Month = if(MONTH(Date_Table[Date])=MONTH(TODAY()),"Yes", "No")

Current_Year = if(YEAR(TODAY())=Date_Table[Year],"Yes", "No")

Current_6Months_Period: please refer to below link to calculate 6 months period.

https://community.powerbi.com/t5/Desktop/DAX-formula-to-return-data-for-last-6-months/td-p/50455

[amitchandak · Accepted solution]

@Boycie92 , refer these new columns

Is Today = if('Date'[Date]=TODAY(),1,0)

This week = if([Date] >= 'today()+-1*WEEKDAY(today() ,2)+1 && [Date] <= today()+ 5-1*WEEKDAY(today(),2) ,1,0)

Month Type = Switch( True(),
Date([Date])= eomonth(Today(),0),1 ,
0
)

las6 6Months Type = Switch( True(),
([Date]) >= eomonth(Today(),-6) && Date([Date])<= eomonth(Today(),0) ,1 ,
0
)

Year Type = Switch( True(),
year([Date])= year(Today()),1,
0
)

Refer : https://www.youtube.com/watch?v=hfn05preQYA