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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vtkendra
Frequent Visitor

Help with Creating YTD Calculation without a Date Table

The source data for my data is already aggregated by quarter and year. There is no date-level detail so I do not have a date table. I need to build YTD total measures for the following:

  • 2021 YTD Q1 = Sum(Q1 wRVUs)
  • 2021 YTD Q2 = Sum(Q1 wRVUs) + sum(Q2 wRVUs)
  • 2021 YTD Q3 = Sum(Q1 wRVUs) + sum(Q2 wRVUs) + sum(Q3 wRVUs)
  • 2021 YTD Q4 = Sum(Q1 wRVUs) + sum(Q2 wRVUs) + sum(Q3 wRVUs) + Sum (Q4 wRVUs)
  • 2022 YTD Q1 = Sum(Q1 wRVUs)
  • 2022 YTD Q2 = Sum(Q1 wRVUs) + sum(Q2 wRVUs)
  • 2022 YTD Q3 = Sum(Q1 wRVUs) + sum(Q2 wRVUs) + sum(Q3 wRVUs)
  • 2022 YTD Q4 = Sum(Q1 wRVUs) + sum(Q2 wRVUs) + sum(Q3 wRVUs) + Sum (Q4 wRVUs)

 

This is my table:

vtkendra_0-1680188151528.png

 

Can someone help me with the DAX syntax to accomplish this? 

2 REPLIES 2
lbendlin
Super User
Super User

I don't think that would help you.  Instead, add a calendar table to your data model and use the standard functions.

@lbendlin  thank you. I have now figured out the solution. I was confused because when I created a date table before and tried to connect my "Q1 2022" data field to the date table, it would not allow me because it's a many to many relationship.

 

What I figured out is that I just need to create a date field for all of my Q1 2022 data and set it to 1/1/2022 to connect to the date table. That's the part I was missing!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.