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 all please help me for dax to get last year value with this kind of filter
this is my current dax
this time i have to use another field as slicer which data type is not number
Thankyou inadvance
Best Regard
firstch
Solved! Go to Solution.
@firstch , what is the year here? In such a case I recommend using Rank on sortable column
Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)
Then have measure
This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))
YTD
Column
Day of Year =datediff([Year Start date] , [Date],Day) +1
Measure
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))
with month
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))
Year and month are any numeric or sortable values , month needs to be number in the above case
refer custom time intelligence here
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
hi @amitchandak
if this reply is dupplication, im aplogize for it
i have try your solution and its says cant compare text with int
could you please try it with this sample data please:suppose this year is 2550/51
year | productionYear | fruit | value |
2551 | 2550/51 | apple | 514823 |
2551 | 2550/51 | guava | 466683 |
2551 | 2550/51 | grape | 741446 |
2551 | 2550/51 | apple | 222318 |
2551 | 2550/51 | guava | 524553 |
2551 | 2550/51 | grape | 842539 |
2551 | 2550/51 | apple | 539855 |
2551 | 2550/51 | guava | 135614 |
2551 | 2550/51 | grape | 858769 |
2551 | 2550/51 | apple | 774507 |
2552 | 2551/52 | guava | 396773 |
2552 | 2551/52 | grape | 662128 |
2552 | 2551/52 | apple | 777911 |
2552 | 2551/52 | guava | 306057 |
2552 | 2551/52 | grape | 979162 |
2552 | 2551/52 | apple | 729226 |
2552 | 2552/53 | guava | 889772 |
2552 | 2552/53 | grape | 841883 |
2552 | 2552/53 | apple | 664174 |
2552 | 2552/53 | guava | 494220 |
2552 | 2552/53 | grape | 353886 |
2552 | 2552/53 | apple | 889931 |
2552 | 2552/53 | guava | 645981 |
2552 | 2552/53 | grape | 840849 |
2552 | 2552/53 | apple | 199105 |
2552 | 2552/53 | guava | 929611 |
2552 | 2552/53 | grape | 692048 |
2552 | 2552/53 | apple | 673923 |
2552 | 2552/53 | guava | 631041 |
2553 | 2552/53 | grape | 55413 |
2553 | 2552/53 | apple | 271165 |
2553 | 2552/53 | guava | 342101 |
2553 | 2552/53 | grape | 42158 |
2553 | 2552/53 | apple | 301398 |
2553 | 2552/53 | guava | 412220 |
2553 | 2552/53 | grape | 181670 |
2553 | 2552/53 | apple | 868329 |
best regard
firstch
@firstch , what is the year here? In such a case I recommend using Rank on sortable column
Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)
Then have measure
This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))
YTD
Column
Day of Year =datediff([Year Start date] , [Date],Day) +1
Measure
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))
with month
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))
Year and month are any numeric or sortable values , month needs to be number in the above case
refer custom time intelligence here
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
hi @amitchandak thank you for you help
this is my sample data suppose year here is 2550/51
year | productionYear | fruit | value |
2551 | 2550/51 | apple | 514823 |
2551 | 2550/51 | guava | 466683 |
2551 | 2550/51 | grape | 741446 |
2551 | 2550/51 | apple | 222318 |
2551 | 2550/51 | guava | 524553 |
2551 | 2550/51 | grape | 842539 |
2551 | 2550/51 | apple | 539855 |
2551 | 2550/51 | guava | 135614 |
2551 | 2550/51 | grape | 858769 |
2551 | 2550/51 | apple | 774507 |
2552 | 2551/52 | guava | 396773 |
2552 | 2551/52 | grape | 662128 |
2552 | 2551/52 | apple | 777911 |
2552 | 2551/52 | guava | 306057 |
2552 | 2551/52 | grape | 979162 |
2552 | 2551/52 | apple | 729226 |
2552 | 2552/53 | guava | 889772 |
2552 | 2552/53 | grape | 841883 |
2552 | 2552/53 | apple | 664174 |
2552 | 2552/53 | guava | 494220 |
2552 | 2552/53 | grape | 353886 |
2552 | 2552/53 | apple | 889931 |
2552 | 2552/53 | guava | 645981 |
2552 | 2552/53 | grape | 840849 |
2552 | 2552/53 | apple | 199105 |
2552 | 2552/53 | guava | 929611 |
2552 | 2552/53 | grape | 692048 |
2552 | 2552/53 | apple | 673923 |
2552 | 2552/53 | guava | 631041 |
2553 | 2552/53 | grape | 55413 |
2553 | 2552/53 | apple | 271165 |
2553 | 2552/53 | guava | 342101 |
2553 | 2552/53 | grape | 42158 |
2553 | 2552/53 | apple | 301398 |
2553 | 2552/53 | guava | 412220 |
2553 | 2552/53 | grape | 181670 |
2553 | 2552/53 | apple | 868329 |
best regard
firstch
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |