Forum Discussion

bgonen7's avatar
bgonen7
Icon for Helper I rankHelper I
4 years ago
Solved

DAX Var for DSO

Can't get the second VAR to work - can someone help

(if today's date is 8/24 then Day number should be 236)

 

 

DSO =

VAR Ratio =  (([OpenAR]/[CY (YTDsales)])--this one works

VAR DaysYTD = DATEDIFF(DATE(YEAR('Calendar'[Date]),1,1),'Calendar'[Date],DAY)+1---this VAR not working

 

RETURN

Ratio*DaysYTD

Need Help
  • bgonen7's avatar
    bgonen7
    4 years ago

    Thanks for the quick feedback.

    I was able to simplify a bit the second VAR (also, first VAR I had to add ()

     

    DSO =
    VAR Ratio = (([Open AR]/[CY(YTDsales)]))

    VAR DaysYTD = [Days YTD]// >>>note I created a measure called [Days YTD] = DATEDIFF(DATE(YEAR(TODAY()),1,1),TODAY(),DAY)

    RETURN

    Ratio*DaysYTD

     

     

3 Replies

  • bgonen7's avatar
    bgonen7
    Icon for Helper I rankHelper I
    4 years ago

    Thanks for the quick feedback.

    I was able to simplify a bit the second VAR (also, first VAR I had to add ()

     

    DSO =
    VAR Ratio = (([Open AR]/[CY(YTDsales)]))

    VAR DaysYTD = [Days YTD]// >>>note I created a measure called [Days YTD] = DATEDIFF(DATE(YEAR(TODAY()),1,1),TODAY(),DAY)

    RETURN

    Ratio*DaysYTD

     

     

  • davehus's avatar
    davehus
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi bgonen7,

    Seems the reference on the second part of the datediff could be your issue. It should probably be

    DATEDIFF(DATE(YEAR('Calendar'[Date]),1,1),MAX('Calendar'[Date]),DAY)+1

     

    Or use TODAY() either

     

    Did I help you today? Please accept my solution and hit the Kudos button.

     

  • MarkLaf's avatar
    MarkLaf
    Icon for Super User rankSuper User
    4 years ago

    Will something like this work for you?

    DaysYTD = 
VAR enddt = TODAY()
VAR startdt = DATE( YEAR( enddt ), 1, 1 )
VAR DaysYTD = INT( enddt - startdt + 1)
RETURN
DaysYTD

     

    The sqlbi example set for DATEDIFF may be useful to check out: https://dax.do/tEsAPbIRk0vStU/