Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
asuprunova
Frequent Visitor

Year Over Year calculation does not work for one year but works for another.

Hello Power BI Community,

I'm experiencing a peculiar issue with my Year-over-Year (YoY) DAX formula in Power BI. The formula works correctly for the years 2018, 2019, 2020, and 2021, but fails to return values for 2022. Here's what I've already checked and confirmed:

  1. Date Table: My Date table includes all dates and covers the entire range, including the year 2022.
  2. Data Table: The table that contains the metrics for YoY calculation has complete and accurate data for each year, including 2022.
  3. Data Model Relationships: The relationships in my data model are set up correctly, with a one-to-many relationship where appropriate.
  4. Data Quality: I've reviewed the raw data for 2022, and it appears to be correct and consistent with previous years.
  5. Filters: There are no filters applied that could potentially exclude the year 2022.
  6. Alternative Approaches: I attempted creating a new page with the same data and formula, but the issue persists.
  7. Software Updates: I've updated Power BI Desktop to the latest version as a first step, but it had no effect on the issue.

At this point, I'm at a loss for what else to try. The formula has been reliable for other years, and nothing ostensibly seems wrong with the data or setup for 2022. If anyone has encountered a similar issue or has any insights on what might be causing this anomaly, your assistance would be greatly appreciated.

Thank you in advance for your help and suggestions! 

asuprunova_0-1700585054779.pngasuprunova_1-1700585111701.pngasuprunova_2-1700585128917.png

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @asuprunova ,

 

Here I create a sample to have a test. My measure is similiar like yours and it will work.

Relationship:

vrzhoumsft_1-1700724290621.png

Measure:

LaborForceTotal = CALCULATE(SUM('LaborForce'[Value]))
YoYLaborForce% = 
VAR PYRecepts = 
CALCULATE(LaborForce[LaborForceTotal],DATEADD('Date'[Date],-1,YEAR))
RETURN
DIVIDE([LaborForceTotal]-PYRecepts,PYRecepts)

Result is as below.

vrzhoumsft_0-1700724209482.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

1 REPLY 1
v-rzhou-msft
Community Support
Community Support

Hi @asuprunova ,

 

Here I create a sample to have a test. My measure is similiar like yours and it will work.

Relationship:

vrzhoumsft_1-1700724290621.png

Measure:

LaborForceTotal = CALCULATE(SUM('LaborForce'[Value]))
YoYLaborForce% = 
VAR PYRecepts = 
CALCULATE(LaborForce[LaborForceTotal],DATEADD('Date'[Date],-1,YEAR))
RETURN
DIVIDE([LaborForceTotal]-PYRecepts,PYRecepts)

Result is as below.

vrzhoumsft_0-1700724209482.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.