Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a bunch of transactions with uniques ids and a Date field. In my sheet, there is a Date Picker from the dashboard bundle, where the user can select a range of dates. I have two KPIs: one for number of transactions for selected interval and one KPI for showing the number of transactions in the same interval last year.
My measure for the last year part does not work:
Count({1<Date={">=$(=AddYears(Min([Date]), -1))<=$(=AddYears(Max([Date]), -1))"}>} [Name of Transaction ID Field])
I can see in the bottom of the set expression window that it evaluates correctly:
I have selected the interval 05.05.2020 - 11.05.2020, but it just shows 0 in the KPI. I know there is data for that interval, because if I select that interval in the date picker, the first KPI shows me the correct amount of transactions.
I suspect it is related to me using the Date field both as dimension and in the actual expression - but I am not smart enough to figure out how to otherwise achieve this.
Appreciate any help.
Got it working now. Seems that I had a problem with the date format. The following expression works for me:
Count({1<Date={">=$(=Date(AddYears(Min([Date]), -1), 'YYYY-MM-DD'))<=$(=Date(AddYears(Max([Date]), -1), 'YYYY-MM-DD'))"}>}[Transaction PCF ID])