topic Re: Calculating YoY without having actual dates in DAX Commands and Tips

Calculating YoY without having actual dates

GPrice508 — Mon, 14 Jul 2025 16:38:10 GMT

I'm struggling with generating YoY measures since we don't capture actual dates --- we only capture by Year. As a result I can't seem to use any of the PBI date math functions. So for this project I have:

Ind = Count of Individuals that participated in a program

Progam = Program

Area = Region

Office = Local Office

Year = Year

For each program and Area/Office I want to show Sum of Ind, and YoY change by year. How would you approach this?

Re: Calculating YoY without having actual dates

Greg_Deckler — Mon, 14 Jul 2025 16:39:21 GMT

GPrice508 Here is a No CALCULATE solution.

YoY = 
  VAR __ThisYear = 2025
  VAR __ThisYear = FILTER( ALL( 'Table' ), [Year] = __ThisYear )
  VAR __LastYear = FILTER( ALL( 'Table' ), [Year] = __ThisYear - 1 )
  VAR __TYSum = SUMX( __ThisYear, [Ind] )
  VAR __LYSum = SUMX( __LastYear, [Ind] )
  VAR __Result = DIVIDE( __TYSum - __LYSum, __LYSum )
RETURN
  __Result

Re: Calculating YoY without having actual dates

FBergamaschi — Mon, 14 Jul 2025 16:47:14 GMT

I prefer to offer you a CALCULATE solution (no religion, just a different approach, you might consider both as a solution), you just need a date table

Calculated table

Date = CALENDARAUTO()

(connect this table to your dates)

Measures

TotalInd = SUM (Table[Ind])

YoY Delta Pct =
VAR TotalIndVAR = [TotalInd]
VAR TotalIndPrevYearVAR = CALCULATE ( [TotalInd], SAMEPERIODLASTYEAR ( 'Date'[Date] )

RETURN
DIVIDE ( TotalINdVAR-TotalIndPrevYearVAR, TotalIndPrevYearVAR )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Calculating YoY without having actual dates

GPrice508 — Mon, 14 Jul 2025 16:57:38 GMT

IME, you need to have a range of contiguous dates to create a Date Table. And you need a Date Table in order toi use the various Date Math PBI functions (Like SAMEPERIODLASTYEAR or PREVIOUSYEAR), As I mentioned in the post, we do not collect dates. We only have a count of Individuals by year.

Re: Calculating YoY without having actual dates

FBergamaschi — Mon, 14 Jul 2025 17:03:47 GMT

Absolutely true, anyway you could use the date table considering the first day of each year

BUT

You want a solution without it

here it is, still with CALCULATE

Measures

TotalInd = SUM (Table[Ind])

YoY Delta Pct =
VAR CurrentYear = MAX ( Fact[Year] )
VAR TotalIndVAR = [TotalInd]
VAR TotalIndPrevYearVAR = CALCULATE ( [TotalInd], KEEPFILTERS (Fact[year] = CurrentYear-1) )
RETURN

DIVIDE ( TotalINdVAR-TotalIndPrevYearVAR, TotalIndPrevYearVAR )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Calculating YoY without having actual dates

GPrice508 — Mon, 14 Jul 2025 19:05:54 GMT

I'm not sure how this logic gets me where I need to be. This approach appears to produce the last two years in the data (MAX(Fact[Year]) and MAX(Fact[Year])-1 but doesn't address the problem of year-over-year for a range of years. What am I missing here?

Re: Calculating YoY without having actual dates

FBergamaschi — Mon, 14 Jul 2025 19:26:10 GMT

Without data and model it is impossible to help quickly, so I am doing my best. The MAX serves to isolate a single year in the filter context (SELECTEDVALUE would have been better but having no data no model and no visual image I can only guess)

Now, I reproduced a secario where I only have the year in a Sales table and want to do what you are asking. It works as you see here

The code is identical to what I sent you before apart from one REMOVEFILTERS call, here it is

Sales = your Sales measure

Sales PY =

VAR CurrentYear = MAX ( Sales[year] )

RETURN

IF (

HASONEVALUE( Sales[year] ),

CALCULATE ( [Sales], KEEPFILTERS (Sales[year] = CurrentYear-1), REMOVEFILTERS( Sales[year] ) )

)

Sales Delta Pct =

VAR CurrentYear = MAX ( Sales[year] )

VAR TotalIndVAR = [Sales]

VAR TotalIndPrevYearVAR = [Sales PY]

RETURN

DIVIDE ( TotalINdVAR-TotalIndPrevYearVAR, TotalIndPrevYearVAR )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Calculating YoY without having actual dates

v-prasare — Thu, 17 Jul 2025 05:50:11 GMT

Hi GPrice508,

We would like to follow up to see if the solution provided by the community member resolved your issue. Please let us know if you need any further assistance.

FBergamaschi, Greg_Deckler , thanks for your prompt response.

Thanks,

Prashanth Are

MS Fabric community support

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Re: Calculating YoY without having actual dates

v-prasare — Mon, 21 Jul 2025 12:40:35 GMT

Hi @GPrice508,

We would like to follow up to see if the solution provided by the community member resolved your issue. Please let us know if you need any further assistance.

Thanks,

Prashanth Are

MS Fabric community support

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Re: Calculating YoY without having actual dates

v-prasare — Thu, 24 Jul 2025 17:28:16 GMT

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.