December 2, 2016 at 8:41 am
So I currently have it so that if the date is the current month, then a red line goes around the column within the matrix report that I have.
What I actually now want is if the date is the 1st, 2nd or 3rd of the current month, then I need the red line to be around the previous month only. If it is past the 3rd of the month then a red line around the current month.
So I previously had this:
=iif(Fields!THEYEAR.Value = CSTR(YEAR(TODAY)), IIF(Fields!THEMONTH.Value = CSTR(MONTH(TODAY)),"Red", "LightGrey"),"LightGrey")
I have a date column that is 01-mm-yyyy format.
I have worked out I probably need some combination of:
=dateadd("m",0,dateserial(year(Today),month(Today),1))
and
=dateadd("m",-1,dateserial(year(Today),month(Today),1))
but am having issues structuring it.
Any help/pointers are appreciated, apologies for it being late on a Friday 😀
December 2, 2016 at 9:55 am
Kazmerelda (12/2/2016)
So I currently have it so that if the date is the current month, then a red line goes around the column within the matrix report that I have.What I actually now want is if the date is the 1st, 2nd or 3rd of the current month, then I need the red line to be around the previous month only. If it is past the 3rd of the month then a red line around the current month.
So I previously had this:
=iif(Fields!THEYEAR.Value = CSTR(YEAR(TODAY)), IIF(Fields!THEMONTH.Value = CSTR(MONTH(TODAY)),"Red", "LightGrey"),"LightGrey")I have a date column that is 01-mm-yyyy format.
I have worked out I probably need some combination of:
=dateadd("m",0,dateserial(year(Today),month(Today),1))
and
=dateadd("m",-1,dateserial(year(Today),month(Today),1))
This is what I came up with... I did mine in steps... IsCurrentMonth is step 1.
IsCurrentMonth
=IIF(YEAR(Fields!TheDate.Value)=YEAR(Today())
AND MONTH(Fields!TheDate.Value)=MONTH(Today()),"Same MoYr", "different")
then
IsStartCurrentMonth
=IIF(YEAR(Fields!TheDate.Value)=YEAR(Today())
AND MONTH(Fields!TheDate.Value)=MONTH(Today())
AND DAY(Fields!TheDate.Value)<=3,"StartOfCurrMonth", "different")
So you could use the second one to set the border color for your cell in your matrix.
There's no need to cast stuff as strings here... dates are stored as numbers, not strings. That's how you can do date math on them. =)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply