Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
Hopefully, this is what you are looking for.
Regards,
Tom
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:
Hopefully, this is what you are looking for.
Regards,
Tom
tom,,,,what can i say amazing......thank you sir...
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |