Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I want an expression for Sum(Sales) from Starting Date to Selected Date where a user selects Year, Month and Day.
Suppose a Date (MM-DD-YYYY) 1/22/2017 is selected (I have data from 1/1/2010), then the Sum(Sales) should be from Start date (1/1/2010) till 1/22/2017.
The Expression in the Data Load Editor is as Given below:
LOAD
"Date",
Month("Date") as Month,
Year("Date") as Year,
Day(Date) as "Day1",
Sales,
Floor("Date") as DateNum
FROM [lib://Folder/Sales data.xlsx]
(ooxml, embedded labels, table is Sheet1);
I have an expression
if(GetSelectedCount(Date)>0,sum({$ <Date={"<=$(=max(Date))"}> } Sales),Sum({<Date = {">=$(=YearStart(Date))<=$(=Date(Max(Date)))"} >} Sales))
This expression gives the correct result when "Date" field is selected.
I want the correct result when the "Year", "Month" and "Day1" fields are selected not the "Date" field.
Cause in the Main Dashboard, The "Date" filter will not be present. Only the "Year", "Month" and "Day1" filter will be present
Maybe this:
sum({<Year=,Month=,Day=,Date={"<=$(=max(Date))"}>}YourMeasure)
Use MakeDate function here.
Hi,
to make this work with Year, Month and Day1 fields you need to "turn off" those selections in the set analysis (they will still be used to calculate Max(Date) inside the set analysis).
if(GetSelectedCount(Date)>0,sum({$ <Year, Month, Day1, Date={"<=$(=max(Date))"}> } Sales),Sum({<Year, Month, Day1,Date = {">=$(=YearStart(Date))<=$(=Date(Max(Date)))"} >} Sales))
Hope this helps.
Juraj
Hi Juraj,
Thanks for the reply
The Expression is working for selections on Year and Month
However it doesn't show the correct answer when the Day1 field is selected. (But it does work work when 2 or more Day1 fields are selected. It then shows the value for the MAX Day1 field selected i.e. if Day1 value like 2 and 10 are selected then it shows the value of 10th, but if only 10 is selected then it gives the wrong answer).
Can you post a screenshot or an example? That would help me to understand what's going on.
Here is a sample Data.
Maybe this:
sum({<Year=,Month=,Day=,Date={"<=$(=max(Date))"}>}YourMeasure)
Thanks a lot
HI
by seeing your post, i thought as you can help you on my issue.
I have one date column at filter panel. when select i date at filter i should get all the previous 90 dates to selected date and also need to calculate another bucketing column as 0 -30,31-60,61-90 as for for last 30 days as 0-30 and next as 31-60 and 61-90.
Can you please help
Please find my requirment indetail with sample
Have date column as below
date
3/20/2019
3/21/2019
3/22/2019
3/23/2019
3/24/2019
3/25/2019
3/26/2019
3/27/2019
3/28/2019
3/29/2019
3/30/2019
3/31/2019
4/1/2019
4/2/2019
4/3/2019
4/4/2019
4/5/2019
4/6/2019
4/7/2019
Need result as below
At Filter Panel selected date 4/2/2019
We should get last 10 days from selected dates as below Need bucket as below
3/24/2019 greater than 6 days
3/25/2019 greater than 6 days
3/26/2019 greater than 6 days
3/27/2019 greater than 6 days
3/28/2019 4-6 days
3/29/2019 4-6 days
3/30/2019 4-6 days
3/31/2019 1-3 days
4/1/2019 1-3 days
4/2/2019 1-3 days