# Display Last Weeks Data Dynamically Based On Date selection in slicer



## Aradhika (Apr 19, 2020)

Hi Team,

I have requirement in my project such as if i select "OrderDate"
From Slicer then based on that date last week sales data should populate in visual.
Example:
Suppose:
If i select 4/12/2020 in slicer then last week data should populate from that date.
If i select 11/22/2019 in slicer then last week data should populate from that date.

Columns i have is Order_date, Sales  - Only two columns i am using...

I've created a couple of DAX Measures. However, that measure is calculating all weeks inspite of 1 Week. 

Thanks for any help you can provide. You've been great.
AD


----------



## gaz_chops (Apr 19, 2020)

Try sumifs, range is less than date in slicer, range is greater than/equal to  date in slicer less 7 (days).


----------



## Aradhika (Apr 20, 2020)

Hi,

Thank you for your quick response....

I've tried but no luck...

Below is the DAX which i have written :

= CALCULATE(SUM(Orders[Sales]),FILTER(ALL(Orders[OrderNewDate]),Orders[OrderNewDate]<=MAX(Orders[OrderNewDate])))


Still its not working...

Please provide DAX if it is possible... as i'm new in Power BI

Regards,
Ravi


----------



## Aradhika (Apr 23, 2020)

Hi All,

Any Update on my request...
Please guide...

Thank you in advance
Aradhika


----------



## JustynaMK (Apr 23, 2020)

Hi Aradhika,

Just to confirm what your objective is - once you select a date from a slicer (e.g. 23/04/2020), do you want the table to show:

the last week's data (16-22 April), as in 7 separate data points, or
the sum of last week's data (16-22 April), so that only one (total) value will be displayed
?


----------



## Aradhika (Apr 25, 2020)

Hi JustynaMK,

Thank you for your reply...

If i select any date(single) from a slicer e.g. 23/04/2020 or 18/04/2020, I want a table or single card to display...

Sum of last week's data (16-22 April) ....
And if select 18/04/2020 then
Sum of last week's data (12-17 April) ....


Once again thank you 
Aradhika


----------



## JustynaMK (Apr 25, 2020)

Thanks for explaining! I think you can try this measure -


```
mSumOrders = 
    var SelectedDate = SELECTEDVALUE(Orders[OrderNewDate])
return
    CALCULATE(
        SUM(Orders[Sales]),
        FILTER(ALL(Orders[OrderNewDate]), 
            Orders[OrderNewDate] < SelectedDate &&
            Orders[OrderNewDate] >= SelectedDate - 7)
    )
```

Here's the result for 23rd of April:






And for 18th of April (please note that it sums 11-17 April, as it extracts -7 days from the selected date):


----------



## Aradhika (May 15, 2020)

Hi JustynaMK,

Sorry for the late reply.... Due to some medical emergency i did not had access to my emails.

Thank you very much for the DAX. 

I will use this in my project and let you know the results....

Regards,
Ravi


----------



## JustynaMK (May 15, 2020)

Hi Ravi - no problem at all! I hope everything is well with you.

Let me know if you need further help with the formula (no rush).


----------



## Aradhika (May 17, 2020)

Excellent Above DAX is Working Great...  

I need a little change.  For example - If i select a date from a slicer and i should get last 3 months sales as separate data points for 3 months.
and 3 months data only one(total) in single card.

Many Thanks,
Aradhika


----------



## Aradhika (Apr 19, 2020)

Hi Team,

I have requirement in my project such as if i select "OrderDate"
From Slicer then based on that date last week sales data should populate in visual.
Example:
Suppose:
If i select 4/12/2020 in slicer then last week data should populate from that date.
If i select 11/22/2019 in slicer then last week data should populate from that date.

Columns i have is Order_date, Sales  - Only two columns i am using...

I've created a couple of DAX Measures. However, that measure is calculating all weeks inspite of 1 Week. 

Thanks for any help you can provide. You've been great.
AD


----------



## JustynaMK (May 18, 2020)

Hi Aradhika,

To retrieve the total in a single card you can use this adjusted version:


```
mSumOrders = 
    var SelectedDate = SELECTEDVALUE(Orders[OrderNewDate])
    var NewDate = DATEADD(Orders[OrderNewDate], -3, MONTH)
return
    CALCULATE(
        SUM(Orders[Sales]),
        FILTER(ALL(Orders[OrderNewDate]), 
            Orders[OrderNewDate] < SelectedDate &&
            Orders[OrderNewDate] >= NewDate)
    )
```

Let me know if that works for you.


----------



## Aradhika (Jun 2, 2020)

JustynaMK said:


> Hi Aradhika,
> 
> To retrieve the total in a single card you can use this adjusted version:
> 
> ...


----------



## Aradhika (Jun 2, 2020)

Hi JustynaMK,

Sorry for delay in Reply.

It is working as expected... Again thank you for ur help and time... 

Regards,
Aradhikar


----------

