Show whole year while slicing on one month - DAX

[Anonymous]

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

[Greg_Deckler]

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.

[Anonymous]

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

[Ashish_Mathur]

Hi @Anonymous,

Try this

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

In the filter/slicer, select any one year.

[Anonymous]

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

[v-huizhn-msft · Accepted solution]

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 2013-09, it show all value of 2013
Best Regards,
Angelia

[ferdsjoseph]

Hi,

Could you please share the PBIX you tested with? I did all the steps you described and reviewed them many times but I'm getting the value for the selected Month-Year showing for every month. 

Best regards,

Ferdinand

[CidcleyBarbosa]

Hi everone,

I did it using ENDOFYEAR Function as below:

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

[Avinash_Nlagali]

considering my case, I don't have the date column, As I have only the month column and in this case, it will cant be used.

Please suggest if you have any.

[Vladisam]

Brilliant! no extra tables, beautiful simplicity!

[Ashish_Mathur]

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.