Sales today expression set-analysis

[Anonymous]

Hi guys,

I want to show the sales for the current day/month/quarter/year. In this example, sales is called [Nett Amount].

The following expression in set-analysis works (i.e.) for sales per day:

Sum({1<InvoiceYear={'2017'}, InvoiceDate={'2-1-2017'}>}[Nett Amount])

To make sure that this expression will also be working in the other months/years, I tried for example this expression for my daily sales:

Sum({$<InvoiceDate = {"$(=Date(Today(), 'DD/MM/YYYY'))"}>} [Nett Amount])

This expression is only giving me 0 values.

Could you guys help me out with this one?

Thanks in advance!:)

Mike

[oknotsen]

If "InvoiceDate" is an actual date (so stored as a number), the Date() function should not be needed. The Date() function is just a "makeup" function anyway, so I would assume you had Date#() in mind anyway.

Does that get you in the right direction?

[CarlosAMonroy]

Hi Mike,

Maybe try this:

sum({<InvoiceDate={"=$(=Date(Today(),'DD/MM/YYYY'))"}>}[Nett Amount])

Thanks,

Carlos M

[sunny_talwar · Accepted solution]

Try this:

Sum({$<InvoiceDate = {"$(=Date(Today(), 'D-M-YYYY'))"}>} [Nett Amount])

You need to use the correct format for the set analysis to work. Also, look here:

Dates in Set Analysis

[Anonymous]

The InvoiceDate is extracted as an number and eventually transformed into a date (DD-MM-YYYY). So as far as I understand, the Date function is completely useless in this example. Am I right?

Regarding to my issue, I still don't really get why the today() function does not work?

[Anonymous]

Hi Carlos,

thanks for your reply!

The expression you've sent me, is giving me the total sales over all time. So that did not really worked as expected.

Thanks,

Mike

[Anonymous]

Hi Sunny,

I'm still getting 0 values with this expression..

In the master calendar, the date format is (DD-MM-YYYY), so that shouldn't be the problem.

I will have a look at the blog you recommended though!:)

[sunny_talwar]

Try with this then

Sum({$<InvoiceDate = {"$(=Date(Today(), 'DD-MM-YYYY'))"}>} [Nett Amount])

Also, replicate your other expression to see if the hardcoded value matches the expression value or not

Sum({1<InvoiceYear={'2017'}, InvoiceDate={"$(=Date(Today(), 'DD-MM-YYYY'))"}>}[Nett Amount])

Also, make sure that you do have data for today in your application.

[CarlosAMonroy]

Did not notice the date format. You have to use the correct one:

InvoiceDate={'2-1-2017'}

InvoiceDate={"$(=Date(Today(),'D-M-YYYY'))"}>}

Thanks,

Carlos M

[Anonymous]

hmmm, that shouldn't be the problem,

As mentioned in my reaction to Onno, I extracted the data as numbers.

I transformed these numbers into a date with DD-MM-YYYY as format.

With this info, it is not fully clear for me why the today function does not work.

Next to that, it's quite strange that a date is presented as 1-1-2017 and not 01-01-2017.

Perhaps I should have a look in to my data first..