Forum Discussion
dynamic days
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
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))
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.
6 Replies
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))
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 productDATESINPERIOD(BMCalendar[Date],MAX(BMCalendar[Date]),-------------- global calendat date-1 * SELECTEDVALUE('How Many days Back'[How Many days Back]) , Day))------------what is parameterwhen 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 andshow me only quantity data from 13th april 2021you help really appriciable.thanksI 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
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.
