Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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] ) ) ) ) )
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] ) ) ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |