Rolling Period Calculation - Last 60 Days from Today's date

[Rsanjuan]

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?

[CheenuSing · Accepted solution]

@Rsanjuan

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.

[Rsanjuan]

@CheenuSing  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!

[Rsanjuan]

 @CheenuSing  and @Habib

Although, I have no run into another issue.  It's calculating the count of the planned meetings and completed meetings in the last 60 days.  However, for the planned meeting it also needs to count the future meetings after today's date.

[Habib]

@Rsanjuan It should be easy now. Instead of subtracting the date from TODAY add the date and make sure you are excluding today 🙂

[Rsanjuan]

@Habib

For some reason, that formula is not working.  I'm getting a syntax error.  Any ideas?

[Habib]

I am unable to see snapshot clearly. Can you please paste formula you are applying.

[Habib]

@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. 🙂