SSAS and SSRS data not tally

72 Views Asked by At

Can someone enlighten me why this is happening? I am doing a SSAS tabular model with one of the table (refer image in the link below) where the calculated cols as follow:

location_rank =COUNTROWS(FILTER('f_p_txn', EARLIER([fk_p_key])=[fk_p_key] &&EARLIER([start_date])>=[start_date]))

end_date =DATEVALUE(LOOKUPVALUE([start_date], [fk_p_key], [fk_p_key], [location_rank], ([location_rank]+1)))

activity status =IF(ISBLANK([end_date]), "Ongoing", "Done")

What has been calculated and presented in SSAS is correct but when I deploy it and create dataset in SSRS, the output is not tally with SSAS. Noticed in SSRS the date some were correct, some the day and month are mixed up and causing the location_rank become incorrect. then i tried to fix change the start_date and end_date column's properties data type to Date, and tried in different date format like dd/mm/yyyy or mm/dd/yyyy, but the problem still persist. What is causing the mess to happen?

tablel in SSAS tabular model

the dataset I create from the query designer from the above ssas table

1

There are 1 best solutions below

0
On BEST ANSWER

i figured it out. Must specify and transform the datatype of the date column to Date type in SSIS before proceed to SSAS.