Slicer 7 months prior

[Anonymous]

Hi,

Is it possible to display the previous 7 months data in my matrix like in the image?

This needs to be done when one month is selected in the reporting period slicer, but to look back 7 months?

Ive attached an image with how I'd like it to look, but obviosuly that's only obtained from highlighting 7 options.

Any help would be appreciated

Thanks

Liam

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Anonymous]

Hi @Ashish_Mathur ,

That is what I'm after but I'm not quite sure how I would translate that to Power BI?

Thanks

Liam

[Ashish_Mathur]

Hi,

The same formulas/methodology/process will work in PowerBI Desktop as well.

[Anonymous]

Hi @Ashish_Mathur ,

I have limited functionality on the file that you have shared.

I have created the same tables with info. Could you please confirm any relationships that exist?

Thanks

[Ashish_Mathur]

Hi,

See this

[Anonymous]

Thanks for your help with this @Ashish_Mathur  @v-shex-msft 

I have managed to get it to work off of a parameter like in the image.

Is there any way that I can have it automatically set at 6 months with no need for filters? So once E.g. in the image 2020-01 is set then it automatically shows the previous 6 ?

My values code currently is:

Sales (last n months) =

CALCULATE (

AVERAGE( vwSnapShotSummary[Value] ),

DATESINPERIOD ( 'Date'[Date],MAX('Date'[Date]), - [N Value],MONTH)

)

Thanks

Liam

[Ashish_Mathur · Accepted solution]

Hi,

Replace the -[N value] with -6

Hope this helps.

[Anonymous]

Thankyou so much for your help with that @Ashish_Mathur 

All sorted!

[Ashish_Mathur]

You are welcome.

[Anonymous]

@Ashish_Mathur  

One last thing - On top of the previous 6 months, how would the future 6 months be inserted into the same query?

Thanks in advance

Liam

[Ashish_Mathur]

You will have to write another formula in which you will replace -6 with 6.

[Anonymous]

Hi @Ashish_Mathur 

TableData =

CALCULATE (

AVERAGE( vwSnapShotSummary[Value] ),

DATESINPERIOD ( 'Date'[Date],MAX('Date'[Date]), -6,MONTH),

DATESINPERIOD ( 'Date'[Date],MAX('Date'[Date]), 6,MONTH))

This displays blank now. Is that how you meant?

Thanks

[Ashish_Mathur]

No.  That is not what i said.  I said you will have to write another formula.

[Anonymous]

@Ashish_Mathur 

Okay, well that won't work as it needs to be in the same measure.

[v-shex-msft]

Hi @Anonymous,

You can extract date field records to create a new table as the source of the slicer. (this table does not have a relationship to original table) Then you can write a measure formula to replace the original value field. (compare current table date and the selected date and replace unmatched records to blank so that matrix visual will auto-hide them)

If you confused about coding formula, please share some dummy data to test.

Regards,

Xiaoxin Sheng