I have my measures written as below in a Table (for all 12 months separately). I want to use Date dimension (End of month for last 12 months) created using Calendar field to create following measure.
If I simply replace the date logic in expression above with Date field then it gives me null result.
Logic I used to create Calendar -
Let varMinDate = Num(Makedate(Year(Today())-1,Month(Today()),Day(Today()))); Let varMaxDate = Num(Makedate(Year(today()),Month(Today()),Day(Today())));
Datefield: LOAD MonthEnd(date($(varMinDate)+IterNo()-1)) AS Datefield AUTOGENERATE (1) WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
Calender: LOAD Datefield AS Date, Year(Datefield) AS Year, Month(Datefield) as Month, Day(Datefield) AS Day, 'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS Quarter