Hello People,
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.
How can I achieve following using set analysis ?
COUNT(
{<
[Invoice Hold Count] -= {0}
,[Payment Method] = {'Direct Debit'}
,[Invoice Hold Date] = {"<=$(=MonthEnd(AddMonths(today(),-12)))"}
,[Invoice Release Date] = {">$(=MonthEnd(AddMonths(today(),-12)))"}
>}
[Invoice Number])
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
RESIDENT Datefield;
DROP TABLE Datefield;
Highly appreciate your help!
Thanks,
