Forum Discussion
Date Feild Conditions
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
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
)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.
6 Replies
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
)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
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.
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?
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.attached is the pbix file for your reference
