Forum Discussion

yigitersann's avatar
yigitersann
Frequent Visitor
3 years ago
Solved

Reading the Dax Code Easily

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 f...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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])