on 11 May 2016 19:19:28
Time intelligence is used frequently when creating reports and it’s a really strong feature, but it could be made even stronger if you had the option to change the base date for this.
Let’s imagine we have a report showing some year to date and last year to date values, if I open this report it will always show the values based on the current date, but very often around month, quarter or year change you would really like to view this result not including the values of e.g. the current month.
Let’s imagine that today’s date is May 11th but I really want to see my report showing the values from Jan 1st to April 30th – if I need to do this today I would have to create a new report to fit this need and you easily end up having a lot of reports showing the same data but with slightly different time filters to fit the needs. This could be solved by allowing the report user to select a time intelligence base date in this case April 30th – when selecting this date, a YTD calculation should return values from Jan 1st to April 30th and a MTD should return values from April 1st to April 30th.
Administrator on 11 May 2017 06:30:39
Check out the new Quick Measures preview in the April release of Power BI Desktop. It includes some build in time intelligence calculations such as year to date and year over year. Learn more here: https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/
- Comments (6)
RE: Time intelligence base to improve value of YTD, MTD.....
We can use DAX to create our custom YTM or MTD function. Time Intelligence function's just a syntax sugar. You can create a Measure using CALCULATE () function to override any filter context.
MonthToDate =
//calculate sales Amount for previous month
=CALCULATE(
SUM(factSales[Amount]),
FILTER(dimDate,
MONTH(dimDate[dateKey])<=
MONTH(CALCULATE
(MAX(dimDate[dateKey]),
ALL(dimDate)
))-1
)
)
RE: Time intelligence base to improve value of YTD, MTD.....
What I would do is include a date, week, and year offset that works with today's date in your date dim. This would allow you to programmatically set your filter to be the prior time period whenever a user opens a report. In your case a month offset of "is less than 0" and year offset of "is 0"
RE: Time intelligence base to improve value of YTD, MTD.....
This sounds like a job for a SQL Server Analysis Services Cube, not Power BI.
RE: Time intelligence base to improve value of YTD, MTD.....
Wouldn't it be better to just make the DAX or MDX query return values for completed months only? You can manually calculate your target period, and intersect any time slices before you pass them to an aggregate. In fact, this is the way it's been solved for years in MDX, replacing the YTD/MTD shorthands. (They do the same operations anyway, but with presets)
After all, the YTD and MTD values are doing their job as intended, and asking a calculation for the 11th, but then actually getting the value for the 30th because of some base-setting sounds a bit confusing to me?
RE: Time intelligence base to improve value of YTD, MTD.....
Any easy ability to set up Financial Years as well as Calendar Years would be much appreciated (maybe a systems, or report level, leveraging the PowerBI template feature). There will likely need to be a rule that says if the Financial Year number is for the year it starts in (current would be FY16), or the year that it ends in (which would make this FY17).
RE: Time intelligence base to improve value of YTD, MTD.....
Is there a reason you can't use a filter to accomplish this? Keep your one report, add a date filter and set it to April 30th. I also sometimes add calculated fields to my date that that make it easy for user to select current and prior year, current and prior month, and current and prior day