Calculate the last month value at month level

1
votes

I want to calculate the last month value in Power BI, knowing that I have my sales table at month level and not at day level (that allows to flag the column as a date column).

Two tables:

  1. One for sales (no contiguous dates)
  2. One flagged as a Time table (with contiguous dates)

A link between the tables on the date column.

I have a formula like this one:

Sales Last Month = CALCULATE(SUM(sales[sales]), PREVIOUSMONTH(time[dateKey]))

It's just not working, any advice?

Adding 2 screenshots.

table in power bi

report in power bi - a filter on the date_key is applied

2
powerbidax
What is the context you're using your measure in. Can you show sample data and visual you are trying to get? Have you tried CALCULATE(TOTALMTD(SUM(sales[sales]), time[dateKey]), PREVIOUSMONTH(time[dateKey]))? This might not be what you are looking for (the month to day sum), but it's difficult to tell without seeing what you're attempting, as everything in DAX is context dependent. – W.B.
The goal is to have a table with the current month sales and the previous mont sales, in a separate column for each. And later the previous year. And for multiuple categories of products. – SQLBlogger
Please show me! I have read what you wrote, but a picture is worth a thousand words. From what you're describing, I believe you're lacking the month context in your table, that's why it's not working, but I cannot say until you show/explain to me, what exactly you put in the table visual and whether there are any filters/slicers put in place - that's what creates context for DAX measures. – W.B.
just posted 2 screenshots, in the 2nd one I'm expecting to have the previous in front... tried many combinations, for informations it workks great for a table with contiguous dates. – SQLBlogger
Thank you, that does explain (I think), let me put together an answer. – W.B.

2 Answers

0
votes

The reason is the following: in the report visual, you are taking columns from your sales table (year, month, first day..., etc). PREVIOUSMONTH works like this - it take first date in the context (from the date column supplied as the first argument) and returns a table of all dates for the previous month. So in theory, this should work.

However, since your visual only contains columns from the sales table, and you most likely have a single direction one to many relationship between your date table and sales, there is no time[date] in the context. Therefore, PREVIOUSMONTH does not return anything!

To fix your problem, I would suggest to add year and month columns to you time table and use those in your visual.

Let me know if I got it right.

EDIT

To expand on why there is no time[date] in the context, I will explain how single direction one-to-many relationships work.

In those cases there is always a table on the one side of the relationship (let's call it a filtering table) and the other table on the many side of the relationship (let's call it filtered table).

When you select (through a slicer/filter/cross-highlight) values from the filtering table, rows in the filtered table are limited to those matching the selection (the filter context) in the filtering table. The same happens when you calculate a measure in a visual (a table for instance). Values that are displayed in individual rows (row context) from the filtering table filter rows from the many side table.

However, it doesn't work the other way round, as it is a single direction relationship. This would work for bi-direcional relationship, but that has its own implications as well.

That's why I suggested putting columns from the time table into the visual, as that will introduce filtering row context for PREVIOUSMONTH function to work.

0
votes

You are defining a calculated column, therefore there is a row context over the current row.

The CALCULATE causes a context transition that transforms each of the columns in the current row to the corresponding filter context.

Therefore, to make it work you must remove every unwanted filter. You might start from this

Sales Last Month =
CALCULATE (
    SUM ( sales[sales] ),
    PREVIOUSMONTH ( time[dateKey] ),
    REMOVEFILTERS ( sales )
)