Forum Discussion
Date Feild Conditions
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.
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?