Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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.
Hope this helps
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.
Hey @gsmith_eqd ,
I recommend reading this article: Time patterns – DAX Patterns
Almost everything regards date/datetime based calculations is described here.
Regards,
Tom
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |