Power BI

Old school excel is out! PowerPlatform first series
- Power Pivot for Excel is still unstable at times and that can be frustrating.
- Maybe you don't have a version of Excel with Power Pivot.
- Maybe you only have 32 bit Office and this can't handle your data models, plus you can't get 64 bit.
- Distributing large Excel workbooks (eg several hundred MBs in size) is an issue.

Reasons for whichh you use Power BI Desktop that can be downloaded and used by anyone. It therefore follows that anyone can use Power BI Desktop as an Excel replacement using the "old world" distribution methods traditionally used for Excel (eg email, distribute via file server, SharePoint etc). Reasons you may want to use this approach include:
- Better, more stable and latest version of Power Pivot and Power Query technologies
- Better UI
- Bi-directional cross filtering
- Leverages the latest visualisation technology delivered as part of Power BI
- You don't have a version of Excel that includes Power Pivot.
- It's free.

Benefits With the Free Version
- Better, more stable and latest version of Power Pivot and Power Query technologies
- Leverages the latest visualisation technology delivered as part of Power BI
- A great range of modern visualisations
- Open source framework (anyone can develop and share new visuals)
- HTML5 responsive design (works on any browser and mobile devices)
- Mobile app integration (it works on cross platform mobiles "out of the box")
- It's free.

DATEADD() and other Date Tips for Filtering Data in Power BI


While working with Dates in DAX, you may have come across an interesting behavior when working with the DATEADD() function. If have used this function in SQL, you have a good idea of how the function works and the parameters you pass to it in order to shift a date forward or backwards. However, this function behaves slightly different in DAX. The main difference is in the parameters passed to the function. Let’s take a look:

SQL Syntax
DATEADD (datepart, number, date )
DAX Syntax 
DATEADD(dates, number_of_intervals, interval)

In SQL, we specify what period we want to shift using datepart; how much we want to shift using number; and to what date using date.

In DAX, we specify to what date using dates; how much we want to shift using number_of_intervals; and what period we want to shift using interval.

The most important distinction between these two functions is the what date parameter. While we can use any expression that resolves to a date/datetime in SQL, when using  DAX we must use an existing date column. In addition to that, that date column must contain the date that you are shifting to in order to return a value, otherwise you will get a BLANK result.

Scenario 1 – Determine Yesterday

Let’s say I want a report that automatically filters my data to show me yesterday’s sales. To do this, I need to compare the dates in my dataset to today’s date. Since I need today’s date to be dynamic (so that it shifts automatically each day in my report), I would be tempted to write something like this:

[Today's Date] = Today()
  --I need this since the first parameter in DATEADD() 
  --requires me to have a Dates column

Is Yesterday = 
   if( Sales[SalesDate] = DATEADD(Sales[Today's Date],-1,DAY), 1, 0)

While the syntax is correct and I won’t get any errors, I also won’t get any dates filtered to Yesterday. The reason being, “yesterday” doesn’t exist in the dates column (“[Today’s Date]”) that I am passing into the DATEADD() function.

The DATEADD() function requires that the date you are shifting to exists in your DATES parameter. So if you have a field that defaults to Today, it will never find any other date using DATEADD().