AS OF DATE Dimension

[akuttler]

Hello, I am trying to create a pivot table with the total credit balances AS OF the end of each month. 

But my 'EndOfMonth' Date dimension is giving me null.

The Dimension 'EndOfMonth' defined as :

=IF(Rangemax(TDate) <= '01-31-2018', 'January 2018',
IF(RangeMax(TDate) <= '02-28-2018', 'February 2018',
IF(Rangemax(TDate) <= '03-31-2018', 'March 2018',
IF(Rangemax(TDate) <= '04-30-2018', 'April 2018',
IF(Rangemax(TDate) <= '05-31-2018', 'May 2018',
IF(Rangemax(TDate) <= '06-30-2018', 'June',
IF(Rangemax(TDate) <= '07-31-2018', 'July 2018',
IF(Rangemax(TDate) <= '08-31-2018', 'August 2018',
IF(Rangemax(TDate) <= '09-30-2018', 'September 2018',
IF(Rangemax(TDate) <= '10-31-2018', 'October 2018',
IF(Rangemax(TDate) <= '11-30-2018', 'November 2018',
IF(Rangemax(TDate) <= '12-31-2018', 'December 2018'))))))))))))

My Data Load script :

LOAD
DATE(TDate,'MM-DD-YYYY') as TDate,
SQL SELECT
CAST(FLOOR(CAST([TransactionDate] as float)) as DATETIME) as TDate

FROM dbo.patientaccountreport

I don't know if I am way off or what I'm missing. 

Thanks in advance.

[Anil_Babu_Samineni]

Try this?

=IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('01-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'January 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('02-28-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'February 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('03-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'March 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('04-30-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'April 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('05-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'May 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('06-30-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'June',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('07-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'July 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('08-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'August 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('09-30-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'September 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('10-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'October 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('11-30-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'November 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('12-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'December 2018'))))))))))))

[PradeepReddy]

try to avoid calculated dimension in charts, if possible create the same in script.
It improves the performance.

Try something like this, in script

Input:
LOAD PatientID, Balance
DATE(TDate,'MM-DD-YYYY') as TDate,
Date(monthend(DATE#(Date,'MM-DD-YYYY')),'MMM YYYY') as TMonthEnd;
SQL SELECT
PatientID, Balance,
CAST(FLOOR(CAST([TransactionDate] as float)) as DATETIME) as TDate;

Left Join(Input)
LOAD
PatientID, TMonthEnd,
Date(max(TDate),'MM-DD-YYYY') as TMaxDate
Resident Input
Group by PatientID,TMonthEnd;

Process_Final:
Load *,
IF(TDate=TMaxDate,'Y','N') as MonthEnd_Flag
Resident Input;
Drop Table Input;

Now Create bar chart (As per your requirement)
with 2 dimensions.. TMonthEnd, Patiend
expression... sum({<MonthEnd_Flag={'Y'}>}Balance)