topic Re: Sales for the last and first selected period in DAX Commands and Tips

Sales for the last and first selected period

Anonymous — Mon, 02 Mar 2020 21:31:53 GMT

Hi All,

I have a excel source with data such as
Sale Date, product name, product type, quantity.

On my report page I am showing the sales trend over the period of date using a stacked bar chart which is easy.

My page has a slicer for the sales date by which user can choose the first date and last date to show the sales trend.

On the same page I want show a card which will display the total sales for each product name, product type for the last selected period in the slicer and also the first selected period in the slicer.

Can you please help?
Thanks

Re: Sales for the last and first selected period

vivran22 — Tue, 03 Mar 2020 05:43:11 GMT

Hello Anonymous,

You may try:

Total Orders (First Date) = CALCULATE ( SUM ( Orders[Order Quantity] ), FILTER ( Orders, Orders[Order Date] = MIN ( Orders[Order Date] ) ) ) Total Orders (Last Date) = CALCULATE ( SUM ( Orders[Order Quantity] ), FILTER ( Orders, Orders[Order Date] = MAX ( Orders[Order Date] ) ) )

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

Re: Sales for the last and first selected period

Anonymous — Tue, 03 Mar 2020 07:11:10 GMT

Thanks for response but please note I have tried this but it won’t work.
What the measure will return is the sales for the last period of product type/category.

For example - the last sales period for product category A was DEC 2019 and for B it was Jan 2020. And the max date selected on the date slicer is 31Jan2020.
I expect the measure to display the sales of product B only and for A it should be blank as we’ve had no sales in jan 2020.
But the dax returns sales of product A in jan 2020 and sales of product B for dec 2019.

Hope this helps

Re: Sales for the last and first selected period

ibarrau — Tue, 03 Mar 2020 11:36:29 GMT

Hi. It sound a bit tricky. Let me see if can get it. Then you need two measures. One for the first period and other for the last. The periods are complete months.

Total Orders (First Date) = CALCULATE ( SUM ( Orders[Order Quantity] ), FILTER ( ALL(Orders[Order Date]), Orders[Order Date] <= EOMONTH( MIN ( Orders[Order Date] ) ; 0) ) ) Total Orders (Last Date) = CALCULATE ( SUM ( Orders[Order Quantity] ), FILTER ( ALL(Orders[Order Date]), Orders[Order Date] > EOMONTH( MAX ( Orders[Order Date] ) ; -1) ) )

With this you can compare the last month and the first month consider in the slicer for the orders.

Hope this works.

Regards,

Re: Sales for the last and first selected period

Anonymous — Tue, 03 Mar 2020 14:23:39 GMT

Do you have a proper Date table in the model? Or you're using the Order Date from the fact table in the slicer?

Best
D

Re: Sales for the last and first selected period

Anonymous — Thu, 05 Mar 2020 11:38:53 GMT

Unfortunately this is not working as it returns all sales until the last selected period but I want to display only sales for the last selected period.

Re: Sales for the last and first selected period

Anonymous — Thu, 05 Mar 2020 11:41:06 GMT

I am not using a date table for this. Would it be easier if I used a date table?

Thanks

Re: Sales for the last and first selected period

ibarrau — Thu, 05 Mar 2020 11:49:56 GMT

Hey, the Date or Calendar Table is very usefull for Time Intelligence calculation. First I suggested something that doesn't need it, but now I really don't understand what you mean with "Last Period".

Let's assume I have a selection from 1-1-2020 to 3-1-2020. There area three months there.

Which are the Last period and the first period?

Knowing this might help us to help you.

Regards,