Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Try this MEASURE
RecordCount_Last30Days = CALCULATE ( COUNTROWS ( TableName ), DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -30, DAY ) )
Hi
Sorry it doesn't seem to calculating properly. I get the same result for 30, 60 and 90 days. Any idea why?
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):
Days_Interval = GENERATESERIES(1;90)and rename "Values" to "Days".
Max_Days_Value = MAXX( KEEPFILTERS(VALUES('Days_Interval'[Days])); CALCULATE(MAX('Days_Interval'[Days])) )
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
Thanks for this. Your link to the sample file is broken. Can you repost?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |