Forum Discussion

selvi's avatar
selvi
Frequent Visitor
8 years ago
Solved

DAX FUNCTION

Hi, I got a business requirement where i have to implement YTD for a given data. If the users select the month of January 2018 they want to see the whole 2017 data along with up to date of January 20...
Need Help
Show and Tell
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    selvi

     

    Interesting, so you want to filter on YTD except for January add the previous year as well.

     

    There are any number of ways of doing this. Assuming you have a Date table, here are some ideas:

     

     

    Custom YTD Amount =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR NumMonths =
    MONTH ( EOMONTH ( MaxDate, -1 ) ) + 1
RETURN
    CALCULATE (
        SUM ( FactTable[Amount] ),
        DATESINPERIOD ( 'Date'[Date], MaxDate, - NumMonths, MONTH )
    )
    Custom YTD Amount v2 =
CALCULATE (
    SUM ( FactTable[Amount] ),
    UNION (
        DATESYTD ( 'Date'[Date] ),
        CALCULATETABLE ( DATESYTD ( PREVIOUSMONTH ( 'Date'[Date] ) ) )
    )
)
OwenAuger's avatar
OwenAuger
Super User
8 years ago

selvi

 

Interesting, so you want to filter on YTD except for January add the previous year as well.

 

There are any number of ways of doing this. Assuming you have a Date table, here are some ideas:

 

 

Custom YTD Amount =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR NumMonths =
    MONTH ( EOMONTH ( MaxDate, -1 ) ) + 1
RETURN
    CALCULATE (
        SUM ( FactTable[Amount] ),
        DATESINPERIOD ( 'Date'[Date], MaxDate, - NumMonths, MONTH )
    )
Custom YTD Amount v2 =
CALCULATE (
    SUM ( FactTable[Amount] ),
    UNION (
        DATESYTD ( 'Date'[Date] ),
        CALCULATETABLE ( DATESYTD ( PREVIOUSMONTH ( 'Date'[Date] ) ) )
    )
)
  • selvi's avatar
    selvi
    Frequent Visitor
    8 years ago

    OwenAuger Thanks it is working...:-)