1

I want to create fiscal year from start-date 01-Jul-2015 to end-date 30-Jun-2017

i have this code now i modified this code according to the give fiscal year 

Cal = 
ADDCOLUMNS (
   CALENDARAUTO ( 3 ),
   "MIndex", MONTH ( EDATE ( [Date], -3 ) ),
   "CalMonth", FORMAT ( [Date], "mmm" ),
   "CalQtr", "Q"
       & CEILING ( MONTH ( [Date] ), 3 ) / 3,
   "CalYear", YEAR ( [Date] ),
   "FinQtr", "Q"
       & CEILING ( MONTH ( EDATE ( [Date], -3 ) ), 3 ) / 3,
   "FY",
   VAR CY =
       RIGHT ( YEAR ( [Date] ), 2 )
   VAR NY =
       RIGHT ( YEAR ( [Date] ) + 1, 2 )
   VAR PY =
       RIGHT ( YEAR ( [Date] ) - 1, 2 )
   VAR FinYear =
       IF ( MONTH ( [Date] ) > 3, CY & "-" & NY, PY & "-" & CY )
   RETURN
       FinYear,
   "FinWeekNo", WEEKNUM ( EDATE ( [Date], -3 ), 2 ),
   "CalWeekNo", WEEKNUM ( [Date], 2 ),
   "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),
   "Day", FORMAT ( [Date], "ddd" ),
   "CustomDate", FORMAT ( [Date], "d/mm" )
)

now how i modified above code

rebma testio
  • 57
  • 2
  • 10
  • Why don't you just use a calendar table? They are easy to set up. Then just create a relationship between the tables. All that complicated DAX is not required. – teylyn Apr 26 '19 at 08:26
  • @teylyn ............ i need to create fiscal year calendar table not just simple calendar table and from that i need to extract quarter, month and year – rebma testio Apr 26 '19 at 09:24
  • So? What keeps you from adding columns into the calendar table for fiscal year, fiscal month, fiscal quarter, etc? It's done all the time. If your fiscal year starts on 1 July, add 6 months to the calendar date. And so on. There are many examples of that on the web. – teylyn Apr 26 '19 at 09:45
  • @teylyn yes i use calendar table but there is some error on this line Calendar = ADDCOLUMNS(CALENDAR (DATE("1-Jul-2015"), DATE("30-Jun-2017"))) will yo plz help me out – – rebma testio Apr 26 '19 at 10:01
  • I'm not sure I understand your problem. In a calendar table, every date will be translated to its respective month, year, fiscal year, fiscal month, etc. Then relate the fact table to the calendar table and all is good. – teylyn Apr 26 '19 at 10:06
  • When I say "calendar table" I mean a real table as a data source, not something constructed with DAX – teylyn Apr 26 '19 at 10:16
  • this is the problem i dont have a data source.. i have to create from DAX – rebma testio Apr 26 '19 at 11:02

1 Answers1

0

You use the CALENDARAUTO(3) function where the argument defines the fiscal year as ending in March. Other than that it also looks at all the datetime columns of your model, and defines the date range accordingly. If you want to hardcode your daterange, just change

CALENDARAUTO(3)

to

CALENDAR("2015-07-01", "2017-06-30")

You might also add each of these as calculated columns instead. That would make reading the code a lot easier.

Casper Lehmann
  • 475
  • 2
  • 13
  • You might argue that this _is_ a calendar table. He is wrapping the calendar in an ADDCOLUMNS function but the resulting table still has a uniquely identifiable Date column without any breaks. Doing everything in one command is messy, I agree, but I don't see the point you are arguing. – Casper Lehmann Apr 26 '19 at 10:15