Dynamic display of columns in matrix (without using slicer)

[theDarkPrince]

Hi everyone, hope you and your families are keeping safe. I'm reaching out here becasue this one requires quite a bit of creative mind and imagination. So, what I want to achieve for a client is to display dynamic columns in a matrix visual. The details are mentioned below :

Data Model :

I have a facts table called Facts1 and a dimension table called Calendar (this one does not have consecutive dates, the Primary Key for this one is YYYY_MM field and I just created a date column by appending a "01" to this YYYY_MM to get a complete date). That Facts1, however, does have dates other than the first day of any month.

Problem Statement :

The columns that should be shown in the matrix visual, I want them to be dependent on current date. For instance, the current month we are in is July. So the matrix should have columns (in this order -->) for the amounts of April-20, April-21 (for us, the FY starts in April), April MoM, May-20, May-21, May MoM, June-20, June-21, June MoM. That's it. In addition to these 9 columns, I want to have 3 columns, which should always be a part of the visual, those would be - FY21 Total (thus far), FY22 Total and YoY.

I want this display of columns to be dynamic in the sense that, once the month of July is complete and we enter in August, 3 additional columns should get added immediately after June MoM. Those 3 columns should be July-20, July-21 and July MoM.

Another important thing that I should mention here is that I want to make this completely independent of user input (no slicers, preferably no bookmarks). Any help/suggestion/guidance is appreciated guys.

P.S. :- I'm tagging certain creative and active members on the community in this post. I hope nobody gets offended due to that. @amitchandak @parry2k @selimovd @Jihwan_Kim 

[Greg_Deckler]

@theDarkPrince I think I have a version of The Complex Selector that does this.

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

[theDarkPrince]

Hi @Greg , that's a very interesting report that you have created. Having said that, I'm afraid it does not fit my requirement where I want an MoM column to appear after each month (for me, the seuqence in which the columns appear in matrix is impoertant). Your selector logic lets one make the column selection dynamic and automatic but that is where it gets limited. Additionally, as it can be observed in the screenshot, the last 3 columns (current FYTD, PYTD, YoY) should always be part of the column selection no matter what. Perhaps, for that part, I'm lacking imagination as to how it could be achieved.

Anyways, like I said, what you proposed is quite fascinating and very creative, I might add. Thanks for that.

- Nachiket