cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Show whole year while slicing on one month - DAX

Hello friends,

I have a Calendar Table with Year and Year-Month columns.

In my report - I have a Year-Month slicer, which allows a user to pick only one month (from the "Year-Month" column).

I need to create a chart that shows my Annual Budget by all month of the selected month's year.

For example, when a user selects "2017-June" - then my chart needs to show all of the 2017's months' budget - from January 2017 to December 2017.

I have to make my slicer only one month for other purposes. 

Currently the problem is that my chart shows only Jun-2017. Maybe I need to create a measure that will catch the selected month's year and calculate the budget for it only...

Please help

Thank you!

Michael

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Show whole year while slicing on one month - DAX

Hi @Anonymous,

You need to create a new table only including [Year-Month] column. Please click "New Table" under Modeling on Home page, type the formula below.Please note there is no relationshio between the New Table and your fact table.

 

NewTable=SELECTCOLUMNS(Table, “Year-Month”, Table[Year-Month])


Please use your table name replace the table in my formula. Create a measure using the formula below, add the measure in your chart.

Result = CALCULATE([Budget],FILTER(Sales,Sales[Year]=SELECTEDVALUE('Calendar'[Year])))


I test it using my sample table and get expected result. I have a 'sales' table with [Year], [Year-Month] and [SALE] value column. I create a Calendar table only including [Year-month]. I create a slicer including Calendar[Year-month], then create a measure using the formula.

Value = CALCULATE(SUM(Sales[SALE]),FILTER(Sales,Sales[Year]=SELECTEDVALUE('Calendar'[Year])))


Add sales[Year-Month] and [Value] measure in the line chart, which returns the right result as follows.

select 2014-01, it show all value of 2014select 2014-01, it show all value of 2014select 2013-09, it show all value of 2013select 2013-09, it show all value of 2013
Best Regards,
Angelia

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Show whole year while slicing on one month - DAX

Correct, you would want to create a measure that has an ALL or ALLEXCEPT filter clause in it or some other similar type of filter that ignores month.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: Show whole year while slicing on one month - DAX

Thanks, @Greg_Deckler but how?

I've tried Calculate([Budget],FILTER(ALL(CalendarTable),CalendarTable[Year]=MAX(CalendarYear[Year]))

But it still shows one month only - the selected one...

Please help

Thanks

Michael

Highlighted
Super User IV
Super User IV

Re: Show whole year while slicing on one month - DAX

Hi @Anonymous,

 

Try this

 

=Calculate([Budget],ALL(CalendarTable[Month]))

 

In the filter/slicer, select any one year.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Anonymous
Not applicable

Re: Show whole year while slicing on one month - DAX

Thanks @Ashish_Mathur,

As I've stated - my slicer is a Year-Month (2017-12), not a Year.

I need this for other report purposes.

So a user always selects a certain Month.

For example, a user selects "2017-08", so I want my chart to show data only for 2017 - by All Months (January through December)

In your proposal you ask a user to select a year, this is not my case - I need the user to only choose a Month.

Please help,

Thank you!

Michael

Highlighted
Super User IV
Super User IV

Re: Show whole year while slicing on one month - DAX

Hi,

 

 

Have you tried my fomrula with a Year-Month selection.  If it does not work, then share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Microsoft
Microsoft

Re: Show whole year while slicing on one month - DAX

Hi @Anonymous,

You need to create a new table only including [Year-Month] column. Please click "New Table" under Modeling on Home page, type the formula below.Please note there is no relationshio between the New Table and your fact table.

 

NewTable=SELECTCOLUMNS(Table, “Year-Month”, Table[Year-Month])


Please use your table name replace the table in my formula. Create a measure using the formula below, add the measure in your chart.

Result = CALCULATE([Budget],FILTER(Sales,Sales[Year]=SELECTEDVALUE('Calendar'[Year])))


I test it using my sample table and get expected result. I have a 'sales' table with [Year], [Year-Month] and [SALE] value column. I create a Calendar table only including [Year-month]. I create a slicer including Calendar[Year-month], then create a measure using the formula.

Value = CALCULATE(SUM(Sales[SALE]),FILTER(Sales,Sales[Year]=SELECTEDVALUE('Calendar'[Year])))


Add sales[Year-Month] and [Value] measure in the line chart, which returns the right result as follows.

select 2014-01, it show all value of 2014select 2014-01, it show all value of 2014select 2013-09, it show all value of 2013select 2013-09, it show all value of 2013
Best Regards,
Angelia

View solution in original post

Highlighted
Advocate I
Advocate I

Re: Show whole year while slicing on one month - DAX

Hi everone,

 

I did it using ENDOFYEAR Function as below:

 

FY:=TOTALYTD([Values];ENDOFYEAR(Calendar[Date]))
Highlighted
Frequent Visitor

Re: Show whole year while slicing on one month - DAX

Brilliant! no extra tables, beautiful simplicity!

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors