DAX Last Year based on custom date dimension

[sabd80]

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.

[v-ssriganesh]

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.

[v-ssriganesh]

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 · Accepted solution]

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.

[amitchandak]

@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...

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]

The calendar is not standard.

[Kedar_Pande]

@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]

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.

[Nabha-Ahmed]

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]

@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]

Try this code 

Dax measure:

LastYearSales :=
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR LastYearStart =
DATE ( YEAR ( MaxDate ) - 1, 1, 1 )
VAR LastYearEnd =
DATE ( YEAR ( MaxDate ) - 1, 12, 31 )
RETURN
CALCULATE (
[Total Sales],
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= LastYearStart
&& 'Date'[Date] <= LastYearEnd
)
)

[sabd80]

@Nabha-Ahmed the above code did not work.
I have replaced the code of LastYearStart and LastYearEnd  to point to the fiscal year start date and fiscal year end date, but it did not work

[sabd80]

@Nabha-Ahmed unfortunitally it does not work, it repeats the yearly figure on month and week level.

This my DAX:

Testing2 Measture LY =

VAR currentYear= MAX ( 'Date'[Fiscal Year] )

VAR LastYearStart = CALCULATE(MAX('Date'[Fiscal Year Start Date]),  FILTER(ALL('Date'[Fiscal Year]),'Date'[Fiscal Year] =  currentYear-1))

VAR LastYearEnd = CALCULATE(MAX('Date'[Fiscal Year End Date]), FILTER(ALL('Date'),'Date'[Fiscal Year] =  currentYear-1))

RETURN

CALCULATE (

        [Measture],

    FILTER (

        ALL('Date') ,

            'Date'[Calendar Date] >= LastYearStart &&

            'Date'[Calendar Date] <= LastYearEnd

)

)

[Nabha-Ahmed]

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

[Ritaf1983]

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]

I tried this but it did not work

[lbendlin]

 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.