Forum Discussion
Advocate IIIRolling Period Calculation - Last 60 Days from Today's date
Hello,
I am trying to determine the number of meetings using a rolling period of the past 60 days from today's date. Here are the steps that I took:
1. Used Matt Mason's Date Dimension function:
http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
2. Created a DAX Measure:
Today=DATE(year(now()),MONTH(NOW()), DAY(NOW()))
3. Did a DAX-Calculated column using the following formula:
Last60Days = if(AND([Date] >= [Today]-60 , [Date] <= [Today] ),1,0)
I believe it's calculating the correct number for each person but the total seems to be off. It seems to just be taking the 60 and adding the +1. I changed it to 90 days, and the total was 91.
Please see the screenshot. Any ideas?
From the Last60Days definition, it puts 1 or 0 against each date row in the calendar table.
You are displaying in the table chart the Last60Days. The measure you want to display is the number meetings in the last 60 days. Where do you get this from? is it the count of rows against each name ?
If it is the count of rows against each name, put that as values in the table chart. Then in the Visual Level filters drag the Last60Days column . clikcon the Last60Days field and the show items when the value is , set it to 1 .
This should work.
Try it out and let me know. If it works please accept it as a solution and also give kudos.
7 Replies
From the Last60Days definition, it puts 1 or 0 against each date row in the calendar table.
You are displaying in the table chart the Last60Days. The measure you want to display is the number meetings in the last 60 days. Where do you get this from? is it the count of rows against each name ?
If it is the count of rows against each name, put that as values in the table chart. Then in the Visual Level filters drag the Last60Days column . clikcon the Last60Days field and the show items when the value is , set it to 1 .
This should work.
Try it out and let me know. If it works please accept it as a solution and also give kudos.
RsanjuanAnonymous I was able to figure out. I was leaving out a value "Status" which is the two type of meetings. I had it like this:
This now works and I verified it with the data itself.
Habib Is there an easier way to figure out the rolling period? I wasn't sure how to do it and saw the Today function from Matt Mason's blog.
Thanks everyone for your suggestions!
- Habib
Continued Contributor
Rsanjuan Two corrections here.
First why you need to use DATE function and NOW? You can get date using TODAY function :)
Second you are confused on number of days. For clarity if you subtract 1 from today's date, it will give you date of yesterday. you are subtracting 1 day but getting data for 2 days including today and yesterday. I think this is your confusion point. :)
