cancel
Showing results for
Did you mean:
Helper I

## calculating last 30, 60 and 90 days

I am trying to calculate the number of records in a table that have occurred in the last 30 (60 and 90) days from a date selected on my slicer.  I have tried multiple formulas without success.  I have a date table also.

I would appreciate any advice.  Thanks

Emma

6 REPLIES 6
Microsoft

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Champion

Try this MEASURE

```RecordCount_Last30Days =
CALCULATE (
COUNTROWS ( TableName ),
DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -30, DAY )
)```

Regards
Zubair

Helper I

Hi

Sorry it doesn't seem to calculating properly.  I get the same result for 30, 60 and 90 days.  Any idea why?

Resolver III

Hi Emma!

This question has more then one solution.

1) A simple way to anterstand a calculation IMHO is create 3 new calculate rows in your Fact table with 3 dates (+30, +60 and +90 days from the current date).

2) Alternate way is a bit comlex, but more flexible (see link bellow):

• I have one Date table, that I use as Fact table to (for this example only, in fact you must have separate fact table).
• And I create new Slicer data table from 1 to 90 days (max. to 365!):
• `Days_Interval = GENERATESERIES(1;90)`
and rename "Values" to "Days".
And in your case  the formula above can be (for ex.): Days_Interval = GENERATESERIES(1;90;30), or list of discret values
•  Then I create a measure:
```Max_Days_Value =
MAXX(
KEEPFILTERS(VALUES('Days_Interval'[Days]));
CALCULATE(MAX('Days_Interval'[Days]))
)```
• Create slicer. It look like this:

```RecordCount_LastXDays =
CALCULATE (
COUNTROWS ( Dates );
DATESINPERIOD ( Dates[Dates]; FIRSTDATE( Dates[Dates] ); [Max_Days_Value];DAY)
)```

This is example PBIX. Enjoy

Helper I

Thanks for this.  Your link to the sample file is broken.  Can you repost?

Resolver III

I

@emmaclarke83 wrote:

It's really strange. I tested from another device and network. Must be OK.

Best regs

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!