Date calculations were something which always seemed very daunting to me and sometimes still to if I'm being honest.
The first time I was asked to create a dropdown which allowed the user to select the date period they wanted to see their data for, I was totally lost because the dropdown was to contain 4 options namely :
Data for the Day
Data for the Week
Data for the Month
Data for the Year
Now, if I created this using the usual method, I would have probably lost it because the method always seems too complicated to me.
This is where a colleague of mine taught me an extremely simple way of doing the same with the help of few simple calculated fields.
Step 1: Let's suppose we have a date field called 'Date' . The first thing I'll do is create a calculated field 'Max Date' : {MAX([Date])}
Step 2: This is where we will create our 4 calculated fields for our 4 dropdown options.
Data for the Day - Cur Day : 'IF DATEDIFF('day',[Date],[Max Date])=0 THEN 1 ELSE 0 END'
Data for the Week - Cur Week : 'IF DATEDIFF('week',[Date],[Max Date])=0 THEN 1 ELSE 0 END'
Data for the Month - Cur Month : 'IF DATEDIFF('month',[Date],[Max Date])=0 THEN 1 ELSE 0 END'
Data for the Year - Cur Year : 'IF DATEDIFF('year',[Date],[Max Date])=0 THEN 1 ELSE 0 END'
The above 4 are simple if else statements which returns me 1 and 0 on the basis of the condition.
The only thing to note in the above formula is to give the date period you want to find the value for and the rest of the part remains constant for all.
Step 3: Now we need to create a parameter which gives me the 4 options in a dropdown.
Step 4: The last step is to create a calculated field which will tell how to use the parameter with our measure values.
The calculation will be as follows:
'If Timeline = "Data for the Day" then sum([Measure Value]*[Cur Day])
elseIf Timeline = "Data for the Week" then sum([Measure Value]*[Cur Week])
elseIf Timeline = "Data for the Month" then sum([Measure Value]*[Cur Month])
elseIf Timeline = "Data for the Year" then sum([Measure Value]*[Cur Year])
end'
The best thing that I like about using this method is that, we can keep using it with such an ease repeatedly with different measure values without writing some formula again and again.
Thanks for reading the blog.
You can reach out to me in case of any doubts or suggestions.