I have a data model with the date table having 5 calendars. The 1st column of the date tavle is CalendarName.
On the report the user is forced to choose 1 CalendarName.
Since date table has multiple calendars (5), 1 day repeats 5 times. Hence the table cannot be marked as date table. So technically I don't have a date table.
In this situation is there any way I can use the time intelligence function like the following?
CLOSINGBALANCEMONTH(<expression>,<dates>[,<filter>])
1 work around is to write dax that gets month number into a variable (varYear and varMonth), and then based on these variables get the last date of the month (varDt), and then based on this variable evaluate the expression for that day using:
Calculate(<expr>,CustomDateTbl['Date']=varDt
Is there any other approach? For example- can I activate the auto date time intelligence and then in the dax I can use date from the fact table's date column. Any suggestions?
You need a date table for time intelligence to work. Simple as that.
There is nothing special about the time intelligence functions - you can write your own equivalents using slightly more verbose DAX. However, your model doesn't sound right though. Even if you have 5 calendars for the end users to choose from, you should still have a proper date table. Why do these 5 calendars not just feed into a separate proper date table?
If you watch this video, you can see Alberto use 2 dates tables and propagate the values from one to the other. It sounds like you should be doing something similar.