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
gsmith_eqd
Frequent Visitor

Creating an table comparing current YTD earnings to previous years over the same period

I would like to create a table YearOverYearYTD from table InvoiceLine that displays the YTD invoicing for each year based on the current date. ColumnInvoicingYTD would display each years invoicing between 1/01 of that year and whatever the month and day of the present date are.

 

InvoiceLine contains the columns TxnDate and InvoiceLineRate which indicate the date of each transaction and the amount.

 

InvoiceYTD should contain four columns: Year, InvoicingYTD, InvoicingPrevYTD, and PercentChange. Here is my model:

 

YearInvoicingYTDInvoicingPrevYTDPercentChange
2020$1000  
2021$1200$1000+20%
2022$1500$1200+25%
2023$1350$1500-10%

 

How could I best accomplish something like this? I'll add that my main issue is the InvoicingYTD column - once that is done, the rest seems somewhat straightforward.

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the TxnDate column to the Date column of the calendar Table.  In the Calendar Table, create calculated column formulas to extract Year, Month name and Month number.  Sort the Month name column by the Month number column.  to your visual, drag the year and Month name column from the Calendar Table.  Write these measures:

Total = sum(InvoiceLine[InvoiceLineRate])

Total YTD = calculate([Total],datesytd(calendar[date],"31/12"))

Total YTD SPLY = calculate([Total YTD],sameperiodlastyear(calendar[date]))

Growth (%) = divide(([Total YTD]-[Total YTD SPLY]),[Total YTD SPLY])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Padycosmos
Solution Sage
Solution Sage

Thanks for the reply. I've been trying to recreate the report in this guide using the measures as it instructs. However, when I actually start populating the visuals, none of the "Previous Year" measures return any values.

 

PBI Issue 1.25.PNG

 

The other thing I'm not understanding is how I can alter these to show not just the totals as of present day, but past totals over the same period. So for instance, if the present date were March 17, then I want a column that compares this years sales through march 17 with other years sales through march 17. It seems like for past months and years it is showing the sales for the complete period but I'm specifically trying to compare it to the equivalent period.

 

Also, is there any way that weeks can be incorporated into this method of reporting? I have created a weeknum column but it's unclear from this tutorial how that might be included. It is my hope to have a report the generates weekly snapshots.

Hope this helps

Padycosmos_0-1674689957545.png

 

 

Thanks for the reply. However, the main issue is that the matrix is not displaying correctly on the report. I was able to get it to this state, but it's not showing the MTD current year or QTD current year values. I don't understand why considering the fact that it is showing the YTD sum.

PBI issue 1.26.PNG

 

Also, the other issue I'm having isn't that I don't know how to do the calculation of % change, but that the previous month YTD output is for the whole period, not for the period which has presently elapsed. So since the data was last refreshed on the 17th, the current YTD output shows data through 17 days, output for previous year MTD contains data for the entire month. I would like to see the comparison between the Current year and previous month so far. So if CY MTD shows data through 17 days, I would like to see the PY MTD through 17 days as well.

TomMartens
Super User
Super User

Hey @gsmith_eqd ,

 

I recommend reading this article: Time patterns – DAX Patterns
Almost everything regards date/datetime based calculations is described here.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.