Forum Discussion

KevinW_SDP's avatar
KevinW_SDP
Helper I
6 years ago
Solved

ALL (Column) behavior issue

I am working on some DAX and it is not working as I expect. I tried using some of the DAX functions to do this, but they weren't quite what I was needing. Also, I am doing this in SSAS which seems to...
All()
Analysis Services
DAX
  • amitchandak's avatar
    amitchandak
    6 years ago

    KevinW_SDP , You can try YTD with following method. Prefer a date calendar do not use Date from fact.

    YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

KevinW_SDP's avatar
KevinW_SDP
Helper I
6 years ago

Thank you for the reply. Below is the code that ended up giving me what I am needing.

Sales Amount PYTD:=
var _max = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

RETURN
    CALCULATE (
        Sum('Sales'[sales_amount]),
        DATESYTD(
            DATEADD('- Date'[Date], -1, YEAR)),
        '- Date'[Date] <=_max
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Here is the insight you wanted.

 

First, sorry to say that but this code is flawed. This piece

 

var _max = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

 

will be wrong if you stumble upon a leap year. Check it out.

 

Secondly,

 

Sales Amount PYTD :=
SUMX (
    FILTER (
        ALL ( Sales[invoiced_date] ),
        YEAR ( 'Sales'[invoiced_date] )
            = SELECTEDVALUE ( '- Date'[YearOrder] ) - 1
            && MONTH ( 'Sales'[invoiced_date] )
                <= SELECTEDVALUE ( '- Date'[MonthOfYearNumber] )
    ),
    [sales_amount]
)

 

gives you a semantic error because the table ALL( Sales[invoiced_date] ) has only one column: invoiced_date. And you are iterating over it trying to extract [sales_amount] which is not present in it.

 

I have a question regarding your measure:

Sales Amount PYTD :=
VAR _max =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
    CALCULATE (
        SUM ( 'Sales'[sales_amount] ),
        DATESYTD ( DATEADD ( '- Date'[Date], -1, YEAR ) ),
        
        -- What is the purpose of this filter?
        '- Date'[Date] <= _max
    )

What's the purpose of the filter marked with my comment? The one with "... <= _max"? Does it in any way contribute to the calculation? 'Cause I think it does not. Can you please explain? Thanks.

 

Best
D