Display Last Weeks Data Dynamically Based On Date selection in slicer

[Aradhika]

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]

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

 

[Aradhika]

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]

Hi All,

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

Thank you in advance
Aradhika

 

[JustynaMK]

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:

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

?

 

[Aradhika]

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]

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]

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]

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]

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