Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am wanting to display a rolling year count by month in a table visual.
Jan2023 would count all records from Jan2022-Jan2023.
Feb2023 would count all records from Feb2022-Feb2023.
This is my forumula. Not every elegant, I know.
_R12M Machines in Warranty =
VAR _1stOfMonthYearAgo =
DATE (
YEAR ( MIN ( dimDate[Date] ) ) - 1,
MONTH ( MIN ( dimDate[Date] ) ),
1
)
VAR _R12MCount =
CALCULATE (
DISTINCTCOUNT ( MachineStats[_Model-Serial] ),
FILTER (
MachineStats,
MachineStats[StartUpDate] >= _1stOfMonthYearAgo
&& MachineStats[_WarrantyEnd] >= MAX ( dimDate[Date] )
)
)
RETURN
_R12MCount
These are the two tables that I am using.
When I use my formula, it counts only the records for the current month, not the past year.
Any help would be appreciated.
Solved! Go to Solution.
@haleswd258 , Based on what I got , In example I am using net measure and DimDate is date
You can use measures like
Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-13,MONTH))
Rolling 13= CALCULATE([Net], WINDOW(-12,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))
@haleswd258 , Based on what I got , In example I am using net measure and DimDate is date
You can use measures like
Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-13,MONTH))
Rolling 13= CALCULATE([Net], WINDOW(-12,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))
Thanks @amitchandak
The calculation I ended up using was:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
90 | |
83 | |
76 | |
64 |
User | Count |
---|---|
144 | |
109 | |
108 | |
100 | |
96 |