Dynamic clustered column chart and single card, with a default month selected?

[WAB_Reports]

Hello all,

This may be something very simple (I hope), but I can't seem to get it to do what I want. I have the following charts created:

1 - clustered colum chart in detail of what I want default (November 2020 data)

2 - a singlecard visual with the total (spend) of the default month

3 - clustered column chart of total spend (colourful bars) and total budget (black bars) for the month indicated

2 and 3 are measures/calculations, with more than 1 table involved.  

I'm looking to be able to let the viewer select a Month / Year (either form the selection below 3 or clicking on one of the columns in it, and having the 1 chart/visual provide the details of the totals.  Below, you can see what happens now if I click one of the options (in this case September 2020):

TLDR: Is it possible then, to have a visual with default data and then change to another specific selected criteria, with the ability to revert back to said data if no selection is made?

[v-rzhou-msft]

Hi @WAB_Reports 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @WAB_Reports 

Due to I don't know your data model, I build two sample tables to have a test.

Budget Table:

Add a YearMonth column in this table.

YearMonth = YEAR(Budget[Date])*100+MONTH(Budget[Date])

Spend Table:

Add a YearMonth column in this table.

YearMonth = YEAR(Spend[Date])*100+MONTH(Spend[Date])

Build a slicer by Budget[YearMonth] column and build measures to achieve your goal.

Spend = SUMX(FILTER(Spend,Spend[YearMonth]=MAX(Budget[YearMonth])),Spend[Spend])

Total = 
VAR _Default = SUMX(FILTER(Spend,Spend[YearMonth] = YEAR(TODAY())*100+MONTH(TODAY())),Spend[Spend])
VAR _Select = SUMX(FILTER(Spend,Spend[YearMonth] = SELECTEDVALUE(Budget[YearMonth])),Spend[Spend])
Return
IF(ISFILTERED(Budget[YearMonth]),_Select,_Default)

Build a stacked column chart by Category column and Total measure.

Build a cluster column chart by Budget[Date], Spend measure and Budget column.

Result is as below.

By default, it will show values in current month.

Select YearMonth in slicer.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[WAB_Reports]

Rico! Thank you ever so much. I haven't had a chance to indepth check this just yet, but it does seem to be exactly what I'm looking for!