Wednesday, November 18, 2015

Power BI: Showing YTD values

Still not all time intelligence functions are working but some of them can be used with DAX. If you have a requirement for showing YTD, QTD and MTD, and they are not available with your either multi-dimensional or tabular model, DAX can be used just like we use in PowerPivot for creating them. However, if you try to use functions like PREVIOUSYEAR, PREVIOUSDAY, it might not work (it did not work for me) as all functionalities related to time intelligence have not been added to Power BI yet.

Here is the way of adding YTD measure to a model created in Power BI extracting data from SQL Server. Once data is loaded, go to data tab (green colored box) and click on new measure (red colored box) button for creating a new measure. Then use the following expression for calculating YTD.

SalesAmountYTD = CALCULATE(SUM(FactInternetSales[SalesAmount]), DATESYTD(DimDate[FullDateAlternateKey]), ALL(DimDate))

Once added, it can be used with desired graphical element;

No comments: