Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hii, iwant to find what is the max and min date from data , but my data was doesnt have the date ..
i have one column in my data called sold on.. in this sold on have only numbers like 42654.24 , from this iwant to find max and min of sales and also YTD
Your numbers, as 42654.24, are not dates but most likely timestamps. 42654 represents the date and the fraction .24 represents the time.
To convert the field into a date do this:
dayname(FieldName)
or this: date(floor(FieldName))
To find the max date do this:
dayname(max(FieldName))
or this: date(floor(max(FieldName)))
but my data will be like this, my requirements is i want to show the
max sales kpi, and YTD sales
what you have is timestamps example; 2022-10-12 10:09:00 AM
you need to create a NEW Date field by removing the time part
create a new datefield as below ; example
Fact:
Load *
, Date(Floor(sold_on)) as sold_date
From SomeSourceFile.xlsx;
Then use below expressions to evaluate YTD and max based on sold_date field
YTD = sum({< sold_date={">=$(=Date(Yearstart(Max(sold_date)))) <= $(=Date(Max(sold_date))) "} >} Sales )
Max Date Sales = sum({< sold_date={" $(=Date(Max(sold_date))) "} >} Sales )
Min Date Sales = sum({< sold_date={" $(=Date(Min(sold_date))) "} >} Sales )