Populate Table with Values based on Lookup Table

[Anonymous]

Hi Expert

Need to find a way to populate the Master Table with three columns to show either Y, X and W based on the Record Date, using the Fiscial Period table as a look up. Using the two date columns month Start and Month End. So for explain if we take the first date in the Master Table 07th May 2018. This falls in the Y Category (based on column ROLL12_COMPLETE_PDS_IND). hence populate Y in Column Y against those dates and so on... 

Test Data

Link: https://www.dropbox.com/s/s96ibzey1ind0ai/Test.pbix?dl=0

[TomMartens · Accepted solution]

Hey,

not sure if this is what you are looking for, but I use this DAX statement to create a calculated column:

Column = 
var thisDate = 'Master Data'[Report Date]
return
MAXX(
    FILTER(
        GENERATE(
            SUMMARIZE(
                'Fiscial Periods'
                ,'Fiscial Periods'[FISCAL_MON_START_DT]
                ,'Fiscial Periods'[FISCAL_MON_END_DT]
                ,'Fiscial Periods'[ROLL12_COMPLETE_PDS_IND]
            )
            ,var startDate = 'Fiscial Periods'[FISCAL_MON_START_DT]
            var endDate = 'Fiscial Periods'[FISCAL_MON_END_DT]
            return
            GENERATESERIES(startDate,endDate ,1)
        )
        ,''[Value] = thisDate
    )
    ,'Fiscial Periods'[ROLL12_COMPLETE_PDS_IND]
)

What's going on:

• The "report date" of the current row is stored in the variable thisDate
• The startdate and enddate are used inside GENERATESERIES to pivot the dates into a column automatically called value
• The table is filtered by the report date of the current row of the master table
• MAXX is used as a table is used

Hopefully, this is what you are looking for.

Regards,

Tom

[Anonymous]

tom,,,,what can i say amazing......thank you sir...