Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Dynamic date based revenue calculation

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

DAX
Direct Query
parameters
Slicer

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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