calculating last 30, 60 and 90 days

[emmaclarke83]

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

[v-jiascu-msft]

Hi @emmaclarke83,

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

[Zubair_Muhammad]

Hi @emmaclarke83

Try this MEASURE

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

[emmaclarke83]

Hi

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

[McCow]

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:
• At last I use modyfied mohammad's expression (thanks @Zubair_Muhammad😞

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

  of course instead "Dates" must be your Fact table.

This is example PBIX. Enjoy

[emmaclarke83]

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

[McCow]

I

---

@emmaclarke83 wrote:

 Your link to the sample file is broken.  

---

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

But this is alternate LINK

Best regs