cancel
Showing results for
Did you mean:
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:

 Year InvoicingYTD InvoicingPrevYTD PercentChange 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
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
Responsive Resident
Frequent Visitor

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.

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.

Responsive Resident

Hope this helps

Frequent Visitor

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.

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.

Super User

Hey @gsmith_eqd ,

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors