cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Rolling 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?

1 ACCEPTED SOLUTION
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
7 REPLIES 7
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Helper IV

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

Helper IV

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.

Responsive Resident

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

Helper IV

@Habib

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

Responsive Resident

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

Responsive Resident

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.