How to have a table show the month in a slicer and the previous month

[crobson29]

Hello everybody,

I have a problem that is really getting me.  I have a report that shows multiple KPIs as line graphs that show cumulative sales for the current month, last month, and the current months budget.  These graphs also have a table next to them that show the current month, last month, and a calculation comparing them.  It looks like this:

I also have a slicer at the top of the page that shows "Current Month" for this month, and the month name for all the others.  The graphs are working perfectly, the problem is the table.  Currently, I have the tables decoupled from the slicer, and the measure has a calculate function to only show the current month and the previous month.  

I have just been asked to have the table react to the slicer.  The problem I am having is that if I turn on the interaction between the table and the slicer, it only shows one month at a time.  I was trying to use the SELECTEDVALUE function, but it didn't seem to work.  Here are the formulas for the Month Slicer and the current measure I have built:

Month_Slicer = SWITCH(

    TRUE,

    M_CALENDAR_BILLING[Month Num] == MONTH(TODAY()), "Current Month",

    M_CALENDAR_BILLING[Month]

)

Containers for table = CALCULATE(

    [Current Month Cumulative (Containers)],

    M_CALENDAR_BILLING[Day] <= DAY(TODAY()),

    M_CALENDAR_BILLING[Month Num]== MONTH(TODAY())-1 || M_CALENDAR_BILLING[Month Num] == MONTH(TODAY())

)

Does anyone have any suggestions?

[Anonymous]

Hi @crobson29 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[DataInsights · Accepted solution]

@crobson29,

Here's an article on the topic. The example DAX can also be used in a measure.

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ 

[crobson29]

I believe this will work, I have an additional complication that I will need to deal with since I am using a DirectQuery connnection which does not allow calculated tables.  I think there is a relatively easy workaround, I just need to put in some time in Power Query.  Thank you