dynamic days

[abc_777]

Dear Concern,

I have total sales and calendar table

I can do previous month sale or last 30 days sale

but these are static values

I need dynamic date value. mean user can put last any number of days and it will show those days sale

Example: if user give last 10 or 12 or 18 days it will show previous sales of 10 or 12 or 15 days

Means user can choose to give previosus any number of days to see sale in that time frame

thanks

[amitchandak · Accepted solution]

@abc_777 , You can use what of parameter for that

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

example

 Rolling N day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-1* selectedvalue(whatif[value]) ,Day)) 

[Anonymous · Accepted solution]

Hi @abc_777 

Please correct me if I wrongly understood your question.

Take 'Calendar Date'[Date] as the standard, and then calculate the date of the previous few days . I've taken the previous three days as an example .

Sum last 3 days = CALCULATE(SUM('Table'[sales]),FILTER('Table','Table'[Date]>CALCULATE(MAX('Calendar Date'[Date])-3) && 'Table'[Date]<=MAX('Calendar Date'[Date])))

Original data :

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@abc_777 , You can use what of parameter for that

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

example

 Rolling N day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-1* selectedvalue(whatif[value]) ,Day)) 

[abc_777]

Hi,

Thanks for your nice solution

I have done followings

Rolling X day = CALCULATE(sum('bm_all_cul bm_sales_dhk_all_cul'[Actual Quantity]), -------- Quantity of product

DATESINPERIOD(BMCalendar[Date],MAX(BMCalendar[Date]),-------------- global calendat date

-1 * SELECTEDVALUE('How Many days Back'[How Many days Back]) , Day))------------what is parameter

when i give 5 then may 2021 increases to 5 more day or if I give 10 then may 9th which is not yet to come.

I need to filter activated and show last 10 days or 12 days from today mean today is 23rd april 2021 so should remove all dates and

show me only quantity data from 13th april 2021

you help really appriciable.

thanks

[abc_777]

@amitchandak,

I did some calculation from -365 to +365 I dont know is it ok or not but when I give +30 its show till end of april data but today is 23rd when i give -10 days its cumulatively goes back one by one but still showing whole April 23 days. not just back 10 days.

I want to see last 10 days total quantity

appriciate you help

[abc_777]

@amitchandak 

I am soo sorry to give you several messages.

I think its working. when I give -20 it shows me previous 20 days but I want some more clear table. you you see at my last message you will see -20 is working but then before that -20 it shows some annonimus calculations. is there any ways i can clean them.

when i increase -20 to -25 then newly 5 more column add in report and so on so on

I hope you understand

thanks

[abc_777]

How can I clean red tick mark portion and when I give data like -13 days or -15 days then they will come accodringly my needs