Forum Discussion
Show only the value for the current Year
Hi everyone,
I think I have a pretty simple question.
How do I have to change this measure in order to ONLY show values for the current year?
(I want to show the accumulated values of the current year only)
ShowOnlyValueForCurrentYear =
VAR maxWeek = MAX('Calendar'[CalendarWeek])
VAR maxYear = MAX('Calendar'[Year])
RETURN
CALCULATE(
[Total Value Orders];
ALL('Calendar'[CalendarWeek]);
FILTER(ALL('Calendar'[Year]); 'Calendar'[Year] = maxYear);
'Calendar'[CalendarWeek] <= maxWeek
)
Thanks in advance for your help! This is a very helpful community 🙂
10 Replies
- FarhanAhmed
Community Champion
If you want to show data for "Current year" you can use "Relative Date" Filtering against your date column in your visual to show current year data
- robertomari2020
Helper I
Thanks for your hint FarhanAhmed , but I would love to resolve it with DAX.
- amitchandak
Super User
robertomari2020 , with a date table
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) //Only year vs Year, not a level below This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))Refer Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38afor week refer
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.- robertomari2020
Helper I
Hi amitchandak,
thanks for your help. Unfortunately, none of your suggested measures work for my intend. 😞
(also, I am already using a date table.)
- v-kelly-msft
Community Support
Hi robertomari2020 ,
Create a measure similarly as below:
Measure = Var _year=YEAR(TODAY()) Return CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])&&YEAR('Table'[Date])=_year))And you will see:
In you case,you may try below dax expression:
ShowOnlyValueForCurrentYear = VAR maxWeek = CALCULATE(MAX('Calendar'[CalendarWeek]),ALL('Calendar')) VAR maxYear = CALCULATE(MAX('Calendar'[Year]),ALL('Calendar')) RETURN CALCULATE( [Total Value Orders]; ALL('Calendar'[CalendarWeek]); FILTER(ALL('Calendar'[Year]); 'Calendar'[Year] = maxYear); 'Calendar'[CalendarWeek] <= maxWeek )I made a sample .pbix file if needed.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!- robertomari2020
Helper I
Hi v-kelly-msft ,
I tried your suggested code, but still getting the same problem. It takes the value of 2019 and also shows this in 2020. (but 2020 should be empty, because I only have values in 2019)
Unfortunately I also couldn't open your file - getting this error message:
Object reference not set to an instance of an object.Thanks for your help so far!
- v-kelly-msft
Community Support
Hi robertomari2020 ,
Could you pls upload your .pbix file to onedrive business and share the link with me?I will write the calculation according to your data.
Much appreciated.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- FrankAT
Community Champion
Hi v-kelly-msft ,
you have attached a PBIX file to your reply, which you addressed to community.powerbi.com at the address...Question: How can I upload a PBIX file to any of my posts in the community?Regards FrankAT- v-kelly-msft
Community Support
Hi FrankAT ,
Just upload your .pbix file to Onedrive business and make a public link to share.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- FarhanAhmed
Community Champion
In column series you are putting year and hence there are no filters applied on it, it is taking all available years.
Your measure will work correctly if you remove year from column series