Dynamic date based revenue calculation

[Anonymous]

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).

[amitchandak]

@Anonymous , You can create slicer on column from date  table

select today

Is Today = if('Date'[Date]=TODAY(),"Today",[Date]&"")

select max date

Is Today = if('Date'[Date]=Max(sales[sales date]) ,"Max Date",[Date]&"")

You can use time intelligence for MTD, QTD ytd etd

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc