Forum Discussion
Calculating YoY without having actual dates
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?
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] )RETURNIF (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]RETURNDIVIDE ( TotalINdVAR-TotalIndPrevYearVAR, TotalIndPrevYearVAR )If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
9 Replies
- Greg_Deckler
Community Champion
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 - FBergamaschi
Super User
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
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.
- FBergamaschi
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) )
RETURNDIVIDE ( 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
v-prasareCommunity Support
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.- v-prasare
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. - v-prasare
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.
