Default values in KPI card for previous month

[Anonymous]

Hi,

I have a set of KPI cards for which I want to show values only for previous month as a default setting. But values should then be able to change for KPI card when we start clicking on charts. I also don't want to have a month slicer because I have a line and clustered column chart showing values for all the months. If I use the slicer for a particular month, then we see only columns for this month whereas we want to see all the months from Jan to Dec as we want to see trend. We also want to be able to select any of these columns in the chart for any month and KPI card should change for the specific month. How can we achieve this? KPI card should have a default setting of July in this case and when we select any other month on chart, KPI should change too. As of now, I tried the following:

1. I first tried to set the filter for KPI card for July( as this is last month currently). But then when we click on other months in my chart, we see blank values in KPI card.

2. Then, I created a new column in my date table as last month which shows yes and no if it is last month. I used this new column in a slicer with Yes and No option. But I run into the same issue, we have blanks on KPI if selected No or if selected any other month on the chart.

I am thinking some kind of measure which says "If nothing is selected on this dashboard, KPI card shows last month's value, Else it shows the selected value). But I am struggling to write this measure. I am still new to DAX and such, so help with example will be really helpful. 

Thanks for your help!

[v-deddai1-msft]

Hi @Anonymous ,

Your requirement is not very clear to me. But I think you may need to use ISFILTERED function in your formula: https://docs.microsoft.com/en-us/dax/isfiltered-function-dax

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

Best Regards,

Dedmon Dai

[vanessafvg]

for your clustered column chart, just set the edit interactions to not filter this bar chart when other stuff is filtered.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

for your kpi card , how did you try filter? can you share your calculation? or what did you do to filter it.

please also share how you are trying to look at last month, there are functions you can use to do that like previous month https://docs.microsoft.com/en-us/dax/previousmonth-function-dax

[Anonymous]

Thanks @vanessafvg! 

1. For clustered chart - That's awesome. I wasn't aware of this feature! Thank you!

 2. For KPI card, I don't have any meaure yet. I have selected months from Jan to July in filter pane for now. However, in an attempt to create a measure that can show July value as default when we first open the dashboard, I did create a column in the Dates table as follows:

Last Month = IF(YEAR(Dates[Date]) = YEAR(TODAY()) && MONTH(Dates[Date]) = MONTH(TODAY()) - 1, "Yes", "No")

Now, I am struggling to create a measure which can probably use this last month value as deafult value for KPI, but still be able to change when user clicks on any other month. Something like ""If nothing is selected on this dashboard, KPI card shows last month's value, Else it shows the selected value)" . I am really struggling to write this measure.

Thanks for your help!

[Anonymous]

Hi @vanessafvg !

Because I am still figuring out how to set the values of previous month as default for KPI card when we open the dashboard, I went ahead and used edit interactions based on what I learnt from you. Now I have a month slicer and edit interaction on it such that column clustered chart doesn't get affected and shows all the columns from Jan to Dec. However, now people want to be able to click on any month here in clustered chart other than already chosen month in slicer and still see its values in KPI card. Is it still possible? For example, 

slicer has June as selected month, clustered chart from Jan to Dec is still seen with the help of edit interactions. But when someone clicks on May or any month other than June on one of the column bars in the chart, the values in KPI card are blank because slicer has June selected. Is it possible to still be able to select any of the bars in the chart and view values in KPI card for months not selected in the slicer. If this works, then this can be the solution. Otherwise, I will still have to figure out a way to keep previous month value as default value in KPI cards which should be able to change when we click on any bar column in the chart.

Please let me know.

Thank you for your help!