Hi,I have a customer date table for finacnial date, I want to calculate the measures for last year. Because I have a customer date table, I can't use the time intelligence functions.I would like to show the last year measures by any dimension like customer or product once the year is selected.I am connecting live to the model so I can't share the data.

Hi sabd80,Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.I am also including .pbix file for your better understanding, please have a look into it:Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.Thank you for using the Microsoft Fabric Community Forum.

DAX Last Year based on custom date dimension

16 Replies

lbendlin
Super User
9 months ago
Because I have a customer date table, I can't use the time intelligence functions.

We have a similar issue. The best way to handle this is via OFFSET(-1) on the Year column of your calendar table.
Ritaf1983
Super User
9 months ago
Hi sabd80
Please relate to the linked goodly's guide:
https://www.youtube.com/watch?v=iqUTHlfHomg

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- sabd80
  Helper IV
  9 months ago
  I tried this but it did not work
Nabha-Ahmed
Super User
9 months ago
hi
When you have a custom date table, standard Time Intelligence functions like SAMEPERIODLASTYEAR() don’t work unless the table is marked as a Date Table and connected properly. Since you’re on a live connection, you also can’t add calculated columns easily.
You can still calculate Last Year measures using DAX with FILTER and MAX on the date table.
✅ Example: Last Year Sales

LastYearSales := VAR SelectedYear = MAX ( 'Date'[FinancialYear] ) RETURN CALCULATE ( [Total Sales], FILTER ( ALL ( 'Date' ), 'Date'[FinancialYear] = SelectedYear - 1 ) )

Explanation:
SelectedYear captures the year currently selected in your slicer.
ALL('Date') removes filters on the Date table.
FILTER picks the rows from the previous year.
[Total Sales] is your existing measure.
✅ Dynamic by Dimension
Because the filter is only applied to the Date table, the measure still respects other dimensions like Customer or Product:

- Customer A → Last Year Sales shows filtered for Customer A - Product X → Last Year Sales shows filtered for Product X

sabd80
- sabd80
  Helper IV
  9 months ago
  Nabha-Ahmed thank you so much for your reply.
  Your solution works very well with Fiscal Year and other dimensions, but when I add month and week to the visual the number is repeated, it does not break it down by those date attributibutes.
  - Nabha-Ahmed
    Super User
    9 months ago
    Hi again
    "The issue was caused because the previous measure filtered only by FinancialYear, so Month and Week could not break down the values. I fixed it by using a date-range–based calculation for the entire previous year, which allows Month and Week to aggregate correctly. Now the measure breaks down properly at all date levels
Kedar_Pande
Super User
9 months ago
sabd80

Live connection workaround for custom date table:

Last Year Value =
CALCULATE(
[Your Measure],
FILTER(
ALL('Financial Date'[Date]),
YEAR('Financial Date'[Date]) = YEAR(MAX('Financial Date'[Date])) - 1 &&
MONTH('Financial Date'[Date]) = MONTH(MAX('Financial Date'[Date]))
)
)
- sabd80
  Helper IV
  9 months ago
  the date calendar is a customer calendar, the start and end of the date is different from normal calendar, and it changes every year, some months starte on the 2nd or on the 27th.
amitchandak
Super User
9 months ago
sabd80 , Even if the customer date table, as long as it has all dates/continuous dates it will work. Unless the calendar is not standard.

We got a new calendar option recently that you can use again if you have a continuous date
https://powerbi.microsoft.com/en-us/blog/calendar-based-time-intelligence-time-intelligence-tailored-preview/

Using offset, window can be another option as suggested by lbendlin
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

If can create new columns, then Rank can help
Power BI Custom Period Till Date (PTD)- https://youtu.be/rQ3Z_LtxwQM
- sabd80
  Helper IV
  9 months ago
  The calendar is not standard.
v-ssriganesh
Community Support
9 months ago
Hi sabd80,
Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.
4889136.pbix61 KB
v-ssriganesh
Community Support
8 months ago
Hello sabd80,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.
v-ssriganesh
Community Support
8 months ago
Hello sabd80,

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.