powerbidaxpowerbi-desktopazure-cost-calculation

PREVIOUSMONTH returns blank rows or wrong data

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:

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.

Solution

  • 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.