cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
selvi Frequent Visitor
Frequent Visitor

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 2018(13 Months data), where as if they select February 2018 it should give only January 2018 and up to date of February 2018.

 

Date               Amount

8/1/2016        200

1/1/2017        100

2/1/2017        101

3/1/2017        103

1/1/2018        90

1/ 15/2018     70

2/1/2018        100

 

From the above example, if the user select January 2018 the result should be = 464, For February 2018 the result should be =260.

 

Can you help me out here how to achieve this with DAX function?

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: DAX FUNCTION

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
OwenAuger Super Contributor
Super Contributor

Re: DAX FUNCTION

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

selvi Frequent Visitor
Frequent Visitor

Re: DAX FUNCTION

@OwenAuger Thanks it is working...:-)

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors