Measure to produce columns in Matrix

[Chris2038]

I am wondering if there is a way to produce a measure that will populate columns in a matrix table. See below my single date selection in a slicer.  It produces a single column. When I do multiple selections, it populates the way I need it.

What I'd like is for a single selection to produce a range of dates. I have tried TopN, but it did not work (date selected, return Top 6 dates from the max date). 

[tamerj1 · Accepted solution]

Hi @Chris2038 
Thank you for posting this important question. Your description of your problem is very clear, yet sometimes more details are required in order to serve you with accurate solutions.
With that said, the answer to your question is yes this is possible. You can follow this link to download a sample file which I've prepared for your situation (at least as described) https://www.dropbox.com/t/rDBaokn7GpAtfJla
In order to simplify the issue for you I created the sample file based on one table (sales table). I will try to descrive the steps in the following screeshots:

Now you can create your filter measure:

Filter = 
VAR DefaultInterval = 3
VAR SelectedInterval =
    IF (
        ISBLANK ( SELECTEDVALUE ( Intervals[Interval (Months)] ) ),
        DefaultInterval,
        SELECTEDVALUE ( Intervals[Interval (Months)] )
    )
VAR SelectedNumIntervals =
    SELECTEDVALUE ( 'Number of Intervals'[# of Intervals] )
VAR DatesInFilter =
    ALL ( 'Date' )
VAR DateSelection =
    SELECTEDVALUE ('Date Selection'[End of Month] )
VAR T1 = 
    FILTER ( 
        DatesInFilter,
        'Date'[Date] = 'Date'[End of Month]
            && 'Date'[End of Month] <= DateSelection
    )
VAR T2 =
    ADDCOLUMNS ( 
        T1,
        "@Ranking", RANKX ( T1, 'Date'[End of Month] ) 
    )
VAR T3 = 
    ADDCOLUMNS ( 
        T2,
        "@Test", MOD ( [@Ranking], SelectedInterval )
    )
VAR T4 =
    FILTER (
        T3,
        [@Test] = 0 || [@Ranking] = 1
    )
VAR T5 =
    ADDCOLUMNS ( 
        T4, 
        "@Ranking2", RANKX ( T4, [@Ranking],, DESC ) 
    )
VAR T6 = 
    FILTER ( 
        T5, [@Ranking2] <= SelectedNumIntervals 
    )
VAR T7 = 
    SELECTCOLUMNS ( 
        T6, 
        "Date", 'Date'[Date], 
        "End of Month", 'Date'[End of Month] 
    )
VAR T8 =
    INTERSECT ( 'Date', T7 )
VAR Result = 
    CALCULATE ( 
        COUNTROWS ( T8 ), 
        REMOVEFILTERS ( 'Date' ) 
    )
RETURN
    Result   

Please don't be terrified 🙂 I can help you go through the steps one by one. Please let me know if you need any help.

[Chris2038]

@tamerj1 I cannot thank you enough for this thoroughly detailed repsonse. This is exactly the solution I was hoping for. Very clever with defining the variables as the 7 intervals.
I'm quite new here and from your response I now undertstand the importance of what @littlemojopuppy had mentioned with uploading a PBIX file and sample data in order to best allow others to help. 

[tamerj1]

@Chris2038 

Thanks to you for sharing such issues and requirements. I'm also new to power bi and keen to learn by trying to answer questions. This is the first coding language I've EVER learned l'm not even into data analysis I'm just a mechanical Engineer . Just started few months ago and already fallen in love with DAX  

[littlemojopuppy]

@Chris2038 please refer to this post about how to get your question answered quickly.  Please post some sample data (or better a pbix with your model!) and what you would expect the outcome to be.

[Chris2038]

Thanks for the tips! I will follow that format going forward.

No different in the example link you shared, I too am having trouble "thinking in DAX" on how to populate dynamic column headers based on single input values. In excel, the model would be as follows; 

The date in the last of the 5 columns is the selected date in Cell B2. 

The table should be 5 headers based on date selected. 

Currently in the PBIX file I have one calendar table which all the fact tables are related to. There is obviously an "end of month field" from Power Query.

I am expecting the table to look like this

[littlemojopuppy]

@Chris2038 

VERY different than described in the article...all you're providing is "this is the expected outcome".  No raw data, nothing about how the data is organized...