Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |