topic Re: YoY% but with a rolling date in DAX Commands and Tips

YoY% but with a rolling date

GJeanes1 — Wed, 11 Oct 2023 15:08:01 GMT

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 ) )

Re: YoY% but with a rolling date

Erokor — Wed, 11 Oct 2023 16:07:46 GMT

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

Re: YoY% but with a rolling date

GJeanes1 — Wed, 11 Oct 2023 17:13:50 GMT

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.

Re: YoY% but with a rolling date

Erokor — Wed, 11 Oct 2023 17:17:59 GMT

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)

Re: YoY% but with a rolling date

GJeanes1 — Wed, 11 Oct 2023 17:38:06 GMT

Erokor

Thanks for your insight, unfortunately, I am going to have to wait on Bravo software, but I will update you once I get it and figure out its mechanics.

Re: YoY% but with a rolling date

GJeanes1 — Sun, 15 Oct 2023 16:30:51 GMT

Erokor

So after having some time with Bravo, it has not changed much of what i am trying to accomplish,
Your measure of

CALCULATE([Some Measure],FILTER(ALL('Date'), 'Date'[Year] = YEAR(TODAY())-1)

has replaced the built in time intelligence of yoy% but i am again trying to break it down even further from calculating the total of one year, and comparing it to another year.
I am trying to break down from the date of today - the date of the last 5 year's todays and get the yoy% percentage.