need help with PY calculation

Question

Hi ,I have created a period table with folliwng scripts. I want to give user flixibilty to select period and then this table is related to date dimension. How can I do the PY calculation when user selects rolling period like rolling one year? How I can design my dashboard? Need help to with these time intelligence calculationPeriod = UNION(ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(TODAY() - 7, TODAY()),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Last 7 days",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 1&nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp;ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(TODAY() - 30, TODAY()),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Last 30 days",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 2&nbsp; &nbsp; ),ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(TODAY() - 60, TODAY()),&nbsp; &nbsp; &nbsp; &nbsp;"Type", "Last 60 days",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 3&nbsp; &nbsp; )&nbsp; ,&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; PREVIOUSQUARTER(DATESQTD('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Last Qtr",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 5&nbsp; &nbsp; ),&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; PREVIOUSYEAR(DATESYTD('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Last Year",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 6&nbsp; &nbsp; ),&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(MIN('Calendar'[Date]),TODAY()),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "All Period",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 7&nbsp; &nbsp; ),&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(MIN('Calendar'[Date]),MAX('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Custom",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 8&nbsp; &nbsp; ) ,&nbsp; &nbsp; &nbsp;ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATE(YEAR(MAX('Calendar'[Date])), 1, 1), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TODAY()&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Current Year",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 9&nbsp; &nbsp; ),&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TODAY() - 28,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TODAY()&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Rolling 4 Weeks",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 10&nbsp; &nbsp; ),&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TODAY() - 365, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TODAY()&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Rolling 1 Year",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 11&nbsp; &nbsp; ),&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TODAY() - 1825, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TODAY()&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Rolling 5 Years",&nbsp; &nbsp; &nbsp; &nbsp; "Sort", 12&nbsp; &nbsp; ))

lbendlin · Answer

Use the WINDOW function for that, or EDATE(-12)

lbendlin · Answer

If you want to give them flexibility you should use the Advanced filtering in the filter pane and teach your users how to use that.&nbsp;

diki-i · Answer

Thanks for your response. But I am looking for PY calculation based on period selected, so that it takes the same number of days dynamically and do the previous period calculation.
If rolling 1 year starts from 5-nov-2022 and ends at 5-nov-2023. Then then the PY will dynamically get the start date and end date.

Forum Discussion

need help with PY calculation

3 Replies

Related content

MoM calculation

Calculation Help

Calculate Tooltips

Hour Calculation

Calculating with Filters