Forum Discussion
Calculating Year-over-Year without a Date table
I'm struggling with generating YoY measures since we don't capture actual dates --- we only capture Year. 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 adn Area/Office I want to show Sum of Ind, and YoY change by year. How would you approach this?
A couple of things
1 - this is a DAX question, please cancel the post and repost it here
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands
2 - please enrich your post as it is impossible to answer it as it is. You need to specify the tables you have, how they are connected and the issue (what do you mean "we only capture only year?")
Otherwise the answer is simply this measure
TotalInd = SUM (Table[Ind])
YoY Delta Pct =
VAR TotalIndVAR = [TotalInd]
VAR TotalIndPrevYearVAR = CALCULATE ( [TotalInd], SAMEPERIODLASTYEAR ( 'Date'[Date] )RETURN
DIVIDE ( TotalINdVAR-TotalIndPrevYearVAR, TotalIndPrevYearVAR )Assuming you have a Calendar table, otherwise create it
Date = CALENDARAUTO()
There is no time intelligence without a date table, so please create it
PLEASE REPOST in the DAX section before replying, this is NOT the right place for this question
If this helped, please consider giving kudos and mark as a solution
mein replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
6 Replies
- Greg_Deckler
Community Champion
GPrice508 Here's 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
A couple of things
1 - this is a DAX question, please cancel the post and repost it here
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands
2 - please enrich your post as it is impossible to answer it as it is. You need to specify the tables you have, how they are connected and the issue (what do you mean "we only capture only year?")
Otherwise the answer is simply this measure
TotalInd = SUM (Table[Ind])
YoY Delta Pct =
VAR TotalIndVAR = [TotalInd]
VAR TotalIndPrevYearVAR = CALCULATE ( [TotalInd], SAMEPERIODLASTYEAR ( 'Date'[Date] )RETURN
DIVIDE ( TotalINdVAR-TotalIndPrevYearVAR, TotalIndPrevYearVAR )Assuming you have a Calendar table, otherwise create it
Date = CALENDARAUTO()
There is no time intelligence without a date table, so please create it
PLEASE REPOST in the DAX section before replying, this is NOT the right place for this question
If this helped, please consider giving kudos and mark as a solution
mein replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi GPrice508 ,
Thank you for the prompt response ryan_mayu , Greg_Deckler and FBergamaschi for the prompt responses!
I hope the suggested solution worked for you. If your issue is resolved, kindly accept the helpful post as a solution — it helps the community identify helpful answers more easily.If still facing issues, feel free to reachout!
Thank you.
Regards,
Pallavi G.
v-hashadapuCommunity Support
Hi GPrice508 , Thank you for reaching out to the Microsoft Community Forum.
We find the answer shared by FBergamaschi is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you.- v-hashadapu
Hi GPrice508 , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.
