Year to date and today sales.
Good day everyone.
I'm triyng to setup a measure in the businnes layer that allows to display in the same row the today sales and year to date sales.
In the information design tool I tried to get this in two ways.
1. Create a YTD_SALES measure that sums the sales and in the were clause added this condition.
year(SALEDATE )=year(getdate()) and
2. created a YTD_SALES2 measure thats sums conditioanally on select
sum(case when year(SALEDATE) =
year(getdate()) and SALEDATE<=
getdate() then SALE_AMOUNT else 0 end)
On webi report none of the above works if I include the SALES measure and YTD_SALES cuz in the query I would like to filter sales for today only.
Any help on this will be appreciated.