PREVIOUSMONTH returns blank rows or wrong data

2.3k views Asked by At

I'm tying to compare Azure Cost Management cost to the previous month. The goal is to understand what resources reduce their costs.

I followed this guide that helped me setup PREVIOUSMONTH this way:

PreviousMonth = CALCULATE(
    SUM('Usage details'[costInBillingCurrency]), 
    PREVIOUSMONTH('Usage details'[date].[Date]))

But this formula only returns a blank column.

So I followed this guide that helped me setup this code:

PreviousMonth = CALCULATE(
    SUM('Usage details'[costInBillingCurrency]), 
    PREVIOUSMONTH('Usage details'[date].[Date]),
    ALLEXCEPT('Usage details','Usage details'[subscriptionName],'Usage details'[resourceGroupName],'Usage details'[ResourceName]  ))

Now values are returned but they are wrong.

So I setup this measure and again the column is empty:

Measure = CALCULATE(
    SUM('Usage details'[costInBillingCurrency]),
    MONTH('Usage details'[date])=MONTH(TODAY())-1,
    YEAR('Usage details'[date])=YEAR(TODAY()))

So how to compare the Azure cost of rescues December VS November?

EDIT: I'm adding new raw data:

Here is the problem:

  • the database "preview" exists in October and November but not in December.
  • the database "dev" exists only in December

This means that if I select December as a current month I should see dev for the current month but not for the previous month

enter image description here

And in the other hand I should see the preview database for the month of November but an empty space for the month of December.

Ideally I would like to use the color Red/Green for the current month and color in green if the costs is decreased, red if the cost has increased.

2

There are 2 answers

2
RADO On BEST ANSWER

You have 2 problems:

  1. Data model is missing a calendar table
  2. Function "PREVIOUSMONTH" is using incorrect field

To fix it, you need to add a proper calendar table to your model, and then use it for PREVIOUSMONTH. I quickly prototyped it for you to prove:

enter image description here

Your data model should look like this:

enter image description here

You can create the calendar table in many ways - as a calculated table in DAX, in PowerQuery, or import from a database or file. I always prefer to import.

For quick prototyping, you can create a calculated table using DAX code like this:

Date = 
VAR MinYear = YEAR ( MIN ( Data[Date] ) )
VAR MaxYear = YEAR ( MAX ( Data[Date] ) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    "Year", "CY " & YEAR ( [Date] ),
    "Year-Month", FORMAT ( [Date], "yyyy-mm" )
)

I added just a couple of fields (year and year-month), but you should create all kinds of fields that are useful for your reports.

Then, connect this new table to table "Data" using date fields. Next, create 2 measures (change names as you please):

Total Cost = SUM(Data[costInBillingCurrency])

and

Previous Month Cost = CALCULATE( [Total Cost], PREVIOUSMONTH( 'Date'[Date]))

Notice that I used date from the calendar table, not DATA table.

Finally, I added "Year-Month" field from the calendar table (not from "Data" table) to your visual, to show that it works. If you want to use a slicer, also use date fields from the calendar table.

Power BI is designed to report from a proper dimensional model ( a star schema), with dimensions and fact tables. If you don't build a correct model, you DAX will be complicated and often give wrong results and poor performance.

8
msta42a On

What you want to do is to modify the current context (row context); We can do this by using the function ALL();

CostPrevMonth = 
var _currentdate = SELECTEDVALUE('Table'[date])
return
CALCULATE(sum('Table'[Cost]), FILTER(ALL('Table'[date]), 'Table'[date] = DATE(YEAR(_currentdate),MONTH(_currentdate)-1,1)))

We can also use one of the new window function in DAX:

CostPrveMonthOffset = CALCULATE(SUM('Table'[Cost]), OFFSET(-1, , ORDERBY('Table'[date])))

enter image description here