Reading the Dax Code Easily

[yigitersann]

Hi everyone,

First of all I am new to PBI.

I have a sample table including measures and report created from it.

For these measures, I'm trying to understand what exactly the code is doing for the sake of DAX practise. 

For example; I have this measure below;

Sales Last 6 Months = 
VAR maxDate = MAX(AllSales[Date])
VAR nValue = IF(MONTH(MAX(AllSales[Date]))=MONTH(TODAY()),0,1)
VAR Result = CALCULATE (
    SUM ( AllSales[Sales revenue] ),
    AllSales[Date]
        >= DATE ( YEAR ( maxDate ), MONTH ( maxDate ) - 6 + nValue, 1 )
        && AllSales[Date] < DATE ( YEAR ( maxDate ), MONTH ( maxDate ) + nValue, 1 )
)
RETURN Result

I want to understand what this DAX code do, but it's not very easy for me because there are lots of data in the table.

So what I have tried was, I entered some data (4-5 rows maximum) and created the same measure with correct necessary table/column names, but I think that's also not very good way because it depends on the DAX code I guess.

So would be glad if someone guide me on how to understand the DAX code of measures with lots of datas inside.

Or easy way for practising?

Thanks in advance

[yigitersann]

@Greg_Deckler First of all, thank you a lot for your high quality explanations.

Also, do you think the best way to understand what the DAX code is doing is to add comments as you did, or can you suggest another way?

In my opinion, DAX Studio is very good for practice, but the code in power does not work in DAX Studio which I don't understand why.

[Greg_Deckler · Accepted solution]

@yigitersann Commenting code is always a recommended best practice for all coding languages, including DAX. You can also use the Description metadata field to help document or explain your calculation as well. 

Also, if you want to really understand what is happening at each step of a calculation, use VAR's and then you can return what is in the VAR as the RETURN for the measure and essentially kind of step through the calculation, seeing what each VAR contains as you go. I often use CONCATENATEX to do this for a table VAR which is also yet another reason why I don't tend to use CALCULATE because CALCULATE obfuscates what is going on internally. https://youtu.be/meh3OkgFYfc

DAX Studio is really more about returning tables of values versus measures that return scalar values (single values, not a table of values). That is why your measure code doesn't work in it.

[Greg_Deckler]

@yigitersann So, not the best, most clear DAX code IMHO, I have annotated what is happening:

Sales Last 6 Months = 
// Get the maximum value of Date column in AllSales table
VAR maxDate = MAX(AllSales[Date]) 
// Return 0 if last sale Date is today's date, otherwise 1
VAR nValue = IF(MONTH(MAX(AllSales[Date]))=MONTH(TODAY()),0,1)

VAR Result = CALCULATE (
    // Sum the Sales revenue column but filtering for where the date is greater than 6 months ago and less than current max value
    SUM ( AllSales[Sales revenue] ),
    AllSales[Date]
        >= DATE ( YEAR ( maxDate ), MONTH ( maxDate ) - 6 + nValue, 1 )
        && AllSales[Date] < DATE ( YEAR ( maxDate ), MONTH ( maxDate ) + nValue, 1 )
)
RETURN Result

Biggest issue is that that measure does not account for rolling between years so isn't any good for like January for example. I think this is more clear, more readable and actually works correctly:

Better Sales Last 6 Months = 
// Max Date in AllSales column
VAR __maxDate = MAX('AllSales'[Date])
// End of month 6 months ago, accounts for between years
VAR __EOM6Months = EOMONTH(__maxDate),-6)
// Calculate the minimum date for our filter using the DAY for __maxDate and YEAR and MONTH from __EOM6Months. So, if today is 10/3/2022, this returns 4/3/2022
VAR __minDate = DATE(YEAR(__EOM6Months),MONTH(__EOM6Months),DAY(__maxDate))
// Filter the table for specified date range
VAR __Table = FILTER(ALL('AllSales'), [Date] >= __minDate && [Date] <= __maxDate)
RETURN
// Use an X aggregator to sum the Sales revenue column
SUMX(__Table, [Sales revenue])