Power BI - My YTD Visuals unable to breakdown into Months

[Jaelah]

Hi everyone. So i have my YTD sales by Year Hierachy. The Month slicer will change the YTD figures accordingly. That is all fine.
Mth breakdown 1.PNG

But when i click on the down arrow icon on the visual to drill down and choose a certain year, it doesn't give me the all the months in the year but just the selected month in the slicer.
Mth breakdown 2.PNG

how do i choose a year and show all the months of data?

[Olly]

Clear your month slicer?

You're saying "I've filtered months, and now I can only see one month. How do I see all months?"

[Jaelah]

there's no workaround to do that? it's set the way it is because of my lack of knowledge. but a different setup could achieve my goal?

in simple terms, i want to see YTD based on a month selection. but i also want to select the chart and see the breakdown. if i simply clear the month slicer, i would see Jan to Dec for the particular year, and not up to the month i selected.

[Olly]

You could achieve this by having an unrelated calendar table for your chart x-Axis, and a measure which compares the unrelated month with the slicer month, returning BLANK() if unrelated month > sliced month, otherwise returning your desired measure value.

Or you could simply use a "between" slicer on the related month number.

[Jaelah]

using the "between" slicer, i managed to get the results.

but i love to use the Month slicer instead, so i experimented with what you advised. couldn't quite get it though.
Month Slicer using the related calendar table?
used unrelated calendar table for x-axis as advised.
unable to get the measure. i don't know a function to compare between 2 different tables.

tried:
New Sales YTD = IF(
doesn't allow a table name.

tried:
New Sales YTD = CALCULATE([Sales YTD],CalendarUnrelated[Month Num]<=
doesn't allow a table name again.

tried:
New Sales YTD = CALCULATE([Sales YTD],FILTER(CalendarUnrelated,CalendarUnrelated[Month Num]<=
doesn't allow ANOTHER table name.

here's the pbix file if it helps:
https://easyupload.io/m6pn9w

thank you!

[AliGW]

Please attach the file here - change its extension from .pbix to .zip in order to upload it.

I cannot download from the file-sharing site without becoming a member, and I am not going to do that - sorry.

[Jaelah]

sure, Ali. i have added the zipped file.

[Olly]

Change your slicer to use CalendarUnrelated[Month].

Change the X-Axis of your Chart to use Calendar[Year Hierarchy].

Add a measure:

Please Login or Register  to view this content.

[Jaelah]

i have a feeling it's a stupid mistake i missed out but i just can't seem to figure out. did what you said and the chart is empty.

tried to breakdown the different parts.
MAX ( 'Calendar'[Month Num] )
this will always give me 12 right? since the slicer is based on CalendarUnrelated[Month Num], it is never filtered down.

MAX ( CalendarUnrelated[Month Num] )
this changes accordingly to the slicer.

so 12 is always going to be greater than whatever month in the slicer (except Dec) no? and it will show a blank when true.

i tried using <= instead of >. i get the chart but every month shows Jan-Dec YTD.

[Olly]

MAX ( 'Calendar'[Month Num] ) will change based on evaluation context. Each category of the chart's x-axis returns a different maximum month number.

Your attached PBIX file works when drilled to Month.

We need a different approach, if we want to calculate the YTD sales for a selected month at the year level, as well as for each prior month, within the same measure.

Try this:

Please Login or Register  to view this content.

[Jaelah]

wow. didn't think it will be complicated. thanks again, olly!