skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


By Kasper de Jonge, crosspost from PowerPivotblog.nl

A while ago I did a PoC using PowerPivot, both to show Self Service BI with PowerPivot for SharePoint but also as a datasource for SSRS reports. I used DAX to solve all difficult request, mostly making use of the Time Intelligence functions.

One of the requests was if we could show a running sum of the last 6 months. I had not done that before, so today we take a look at how to do this with DAX. It appeared to be rather easy 🙂

Again we use the almighty Calculate function to change the context of the row we are in. We want to do a sum of all the rows of the last 6 months of data. We use the DATESINPERIOD function to get the last 6 months of dates.

The function DATESINPERIOD has 4 parameters, first is the column containing the datarange, second is the start date. We can get it by getting the last date in context, we use LASTDATE to get this. As last two parameter we can give intervals to subtract or add from the start date. In our scenario we want to subtract 6 months from the last date.

This gives the following formule:

=IF(ISBLANK(sum(FactInventory[DaysInStock])),BLANK(),
CALCULATE(sum(FactInventory[DaysInStock]),
DATESINPERIOD(DimDate[DateKey],
LASTDATE(DimDate[Datekey]),-6,MONTH)))

Again not too hard 🙂

I put in a ISBLANK to check if we have values for the current month, we are not interested in the future 🙂

This gives us the following result:

I decided to check out the DATESBETWEEN as well, the DAX function below gives the same result:

=IF(ISBLANK(sum(FactInventory[DaysInStock])),BLANK(),
CALCULATE(sum(FactInventory[DaysInStock]),
DATESBETWEEN(DimDate[Datekey],
FIRSTDATE(DATEADD(DimDate[Datekey],-5,MONTH)),
LASTDATE(DimDate[Datekey]))))

The DATESBETWEEN function can be used to return a table of dates between a start and a end date.
As end date we need to get the last date that is available in the current context. We can determine this by doing LASTDATE(DimDate[Datekey]). The first date is a little more confusing. We need to get the date 6 months ago, of course we can use the DATEADD function. This will return us a a set of dates of the current context, in our sample we get a set of all dates in the month. Using firstdate we get the first date of this set to start our datesbetween function.

This Post Has 6 Comments
  1. This is very interesting. However, there’s something I still don’t know how to do and it’s getting me crazy: I want the first date to be the first selected date that is shown in the pivottable. I mean, if I have months as rows and I select in the sliders 3 months, I want to running sum to do 1 month for the 1st one, 2 months for the 2nd one and 3 months for the third one. How can I do that?

    Example: I sell 10 in January, 20 in February, 15 in March and 10 in April. If I select in my sliders February, March and April, I want to have as answer 20 for February, 20+15 for March and 20+15+10 for April.

    Thanks in advance!!

  2. i need running total only for specific field without date?
    no memo in out balance
    1 dx01 500 500
    2 snl xo 100 400
    3 delo 200 200
    4 skii 200 400

    so i need DAX for balance column
    thanks

  3. Thanks for sharing this information, I just want to know one more think, how I can select specific date range in datesbetween function ?
    ex. start date= 14/02/2015
    end date= 15/07/2015

  4. Thank you for an interesting webpage. This has helped me, but the formula restarts every January – so the running sum is only working the first 12 months. Is it possible to keep the formula from recalculating in January?
    Best regards

  5. Hi Kasper and readers. I found this post as a part of researching a particular problem I am trying to solve.

    My problem has a twist – not only do i need a sum total of sales from a 6 month date range ( in my case it is a 12 month range) but i need that total to be from sales orders that were transacted only by “Members” that were registered 12 months ago (or previous to that).

    So, if “Membership_Date” was on or before Today() – 365 days, then use the “Membership_ID”,
    Sum all “Transaction_Total” (for all valid IDs) that occurred in the last 12 months.
    In other words, i need to sum the total of all transactions that occurred in the last 12 months but only if the transaction was for a member that has been registered for at least a year.
    I have three tables involved: DATE table, MEMBERS table, which includes Membership_ID and Membership_Date, FACT table which includes Membership_ID, Transaction_Date, Transaction_Total
    Any direction is greatly appreciated.

Leave a Comment or Question