Forum Discussion
YoY% but with a rolling date
So I have a quick measure that looks at YoY% but I need to have it as a rolling date (automated so it sees that the date is different so if reflects the date) so look at today's date but last year's (2022), the year before (2021), and the year before (2020).
Count of Reporting Number YoY% =
IF(
ISFILTERED('Initial Data'[Date_and_Time]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR =
CALCULATE(
COUNTA('Initial Data'[Reporting Number]),
DATEADD(
'Initial Data'[Date_and_Time].[Date],
-1,
YEAR
)
)
RETURN
DIVIDE(
COUNTA('Initial Data'[Reporting Number])
- __PREV_YEAR,
__PREV_YEAR
)
)
5 Replies
- Erokor
Resolver II
I highly suggest utilizing a Date table rather than the built in Time-Intelligence on a date in your fact table. UNLESS you are completely certain you only have one entry for each date, and that you have an entry for each date.
For example if I have a date table, related to my date column. I can simply iterate over that date table, and filter the range of dates I need using logic.
An example of this is below:
VAR CurrDate = 'Date'[Date]
VAR DatesInRange = FILTER(ALL('Date'), 'Date'[Date] = CurrDate - 31 && 'Date'[Date] <= CurrDate)
VAR MovingTotal = CALCULATE([MeasureforAverage], DatesInRange)RETURN
MovingTotal
Erokor
I appreciate your time,
So I have 5 years of data. I am certain that I have maybe 3 or 4 days where there are zero incidents, but the majority of days for the year have at least 1 incident.
So I am looking for something that continues to update as "today" instead of having the measure above and having a day filter on my page.
I want it to read today - 1 year for a total and from there I might be able to get a YoY% difference.- Erokor
Exactly, which you will require a Date table for - this can then be linked to your date column. (You can use Bravo by SQLBI to add one easily).
When you have this you can use Time intelligence CALCULATE() modifier functions such as SAMEPERIODLASTYEAR(). You can also use Relative columns that good date tables have in them. Or CALCULATE([Some Measure],FILTER(ALL('Date'), 'Date'[Year] = YEAR(TODAY())-1)
