Hi,
I have the following case:
I am building a report based on an Excel file hosted on a SharePoint Online environment. The file basically contains CRM opportunity lines with creation date, modified date and expected revenue as relevant columns. I have also created a relatively standard calendar/date table.
Since the Excel file is constantly being fed with new data I am looking for a way to dynamically calculate total expected revenues within a given timeframe (preferably determined by a date slicer). The selected date (or period like Q1 2022), would determine old_revenue as the sum of expected revenue in the period prior to the period selected and new_revenue as the sum of expected revenue in the selected time period.
So far my research has led me to Dynamic M Query Parameters which looks like it could have been a viable solution, but since my data source is Excel based, I don't have the option to use DirectQuery.
Another option I have succesfully tried is hard coding measures that calculate old_revenue as getting the first day of today's month and then summing the revenue of the month prior to that date but this would mean I would have to hard code all possible options (week, month, quarter and year).
