Welcome in Dinesh Patel Blog

DAX for filtering data up to given Year in Power BI Report: Create MaxDateKey measure in FactFinancial table using below DAX MaxDateKey = CALCULATE(MAX('Date'[DateKey]), ALLEXCEPT('Date', 'Date'[FYYear])) Revenue = CALCULATE(sum(FactFinancial[Amount] ),DimAccount[AccountType] = "Income", FILTER(DimDate, DimDate[DateKey] <= [MaxDateKey] )) All other measure like QoQ and Total Amount you apply below logic Revenue = CALCULATE(sum(FactFinancial[Amount] ),DimAccount[AccountType] = "Income", FILTER(DimDate, DimDate[FYYear] = max('Date'[FYYear]) )

Find installed R Packages in SQL/R Process sp_execute_external_script @language = N'R', @script = N'OutputDataSet <- data.frame(installed.packages())' Install Additional R Packages https://msdn.microsoft.com/en-in/library/mt591989.aspx  Revolution R Enterprise inside SQL Server EXECUTE   sp_execute_external_script @language = N'R' ,@script = N' salesData <- InputDataSet fit <- lm(SalesAmount ~ Year + Quarter, data = as.data.frame(salesData)) data2017 <- data.frame(Year=2017, Quarter=1:4) sales2017 <- predict(fit, newdata=data2017) style <- c(rep(1,14), rep(2,4)) salesForecast <- data.frame(data2017, sales2017) library(sqldf) OutputDataSet <- sqldf("select Quarter, Year, SalesAmount from salesData UNION select Quarter, Year, sales2017 from salesForecast") ' ,@parallel = 1 ,@input_data_1 = N'select d.CalendarQuarter Quarter, CalendarYear Year, sum(f.SalesAmount) SalesAm...

DAX Functions for YTD and MTD: =TOTALYTD(SUM(Finance[Amount]),Time[FullDateAlternateKey]) = Current_Year_YTD =TOTALYTD(SUM(Finance[Amount]),PARALLELPERIOD(Time[FullDateAlternateKey],-1,year)) = Last_Year_YTD =TOTALMTD(SUM(Finance[Amount]),Time[FullDateAlternateKey]) = Current_Year_MTD =TOTALMTD(SUM(Finance[Amount]),PARALLELPERIOD(Time[FullDateAlternateKey],-1,year)) = Last_Year_MTD =CLOSINGBALANCEYEAR(SUM(Finance[Amount]),Time[FullDateAlternateKey]) = Full_Year =CLOSINGBALANCEYEAR(SUM(Finance[Amount]),PARALLELPERIOD(Time[FullDateAlternateKey],-1,year)) = Previous_Year_Full =CLOSINGBALANCEMONTH(SUM(Finance[Amount]),Time[FullDateAlternateKey]) = Current_Month_Full =CLOSINGBALANCEMONTH(SUM(Finance[Amount]),PARALLELPERIOD(Time[FullDateAlternateKey],-1,month)) = Previous_Month_Full =CLOSINGBALANCEMONTH(SUM(Finance[Amount]),PARALLELPERIOD(Time[FullDateAlternateKey],-1,year)) = Last_Year_Current_Month_Full =CLOSINGBALANCEMONTH(SUM(Finance[Amount]),PARALLELPERIOD(Time[FullD...