Is there a way to parameterize measures to avoid creating a lot of them?

[sanimesa]

(Sample PBIX and data links below)

I am creating reports for clients to replace a legacy tool (Market Monitoring and Analytics - Mapics XA (MMA)) they use to extract sales data. The tool allows users to add arbitratry number of columns (e.g. specific month or period, specific month or period last year, YTD,MTD, YTD This year, YTD Last year) dynamically to a table.  Since Power BI tables/matrices are static for the viewer, as a compromise we are creating a large number of pages with all possible combinations. The users do not like matrices because when the data is downloaded the months appear in rows as opposed to columns. So, I am planning to create measures for each month this year and last year (please see screenshot). 

Is there an easy way to create many of these measures with parameters or perhaps easily copying and changing them? Has anyone else dealt with this type of a use case and are there better ways?

A sample PBIX file available here: https://1drv.ms/u/s!AqfAbaFIezosbPGHjn4KuZrsZQQ

The sample data of this report comes from https://community.tableau.com/docs/DOC-1236

The custom calendar has the Month boundaries we need. 

[Ashish_Mathur]

Hi,

You should definitely not need to create one measure per month.  Looks like the data is not well structured.  Share the data structure and also show your expected result.

[sanimesa]

@Ashish_Mathur The data structure is as below - very standard actually:

Invoice Table:

Invoice Number, Invoice Date, Line Number, Customer Number, Item Number, Total Amount, Total Cost, Shipped Quantity, Sales Rep Number

There is a similar order table.  Using a Calendar table I found from this forum using power query, many to one relationship between calendar date and invoice date. 

I had  to use a Table and not a Matrix since if I add Month as a hierarchical field on columns, it still downloads months in rows. User needs to download data where he sees the data in columns. A screenshot of my visual is below. 

[Ashish_Mathur]

Hi,

If you already have a Calendar Table and the relatioship is established, you should be able to use the Date/Time Intelligence functions such as SAMEPERIODLASTYEAR().  I can help better only once i see yoru PBI file.

[sanimesa]

@Ashish_Mathur I just could not find a way to avoid creating measures for each month. Unfortunately uploading the file after obscuring data will be too much effort. 

[v-xjiin-msft]

Hi @sanimesa,

Without sample data, it is hard for us to provide a proper solution. 

Help us also means help you.  No need to share us your entire data, just part of them with excel like three or four months. We need these sample data and the desired result to get the right direction.

Thanks,
Xi Jin.

[sanimesa]

@v-xjiin-msft Edited and added sample PBIX/data above. Perhaps it would help if sample data sets were provided to work on the most common problems. 

Thanks! 

[Greg_Deckler]

Can't place my finger on it, but something seems odd about this scenario.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[sanimesa]

@Greg_Deckler I did not add sample data since it is really very standard sales data. Will add some more details though.

I am really seeking design pattern solution ideas.