Identify the missing sections in a table

[Anonymous]

Here's the sample model that helps to identify the missing sections on my real time scenario.

There is a table which has different sections - I am extracting only the July, Aug, Sep months of data that has missed a section in a month. 

ID	Sub	Year	Month
1	Section1	2022	Jul
1	Section1	2022	Aug
1	Section1	2022	Sep
1	Section1	2022	Oct
2	Section2	2022	Jul
2	Section2	2022	Oct
3	Section3	2022	Aug
3	Section3	2022	Sep
3	Section3	2022	Oct

Expected output: ( Only these values has to present as table in report )

2	Section2	2022	Aug
2	Section2	2022	Sep
3	Section3	2022	Jul

Kindly suggest any possibilities to resolve it in power bi, Thanks in advance!

[daXtreme · Accepted solution]

Here you are. File attached.

[Anonymous]

@daXtreme  I need one more hint/support in this method. In advance you stated 2023 data as well for missing values in "T_FullMonths". In visual at 'Absent' checkbox it lised 2023 and sections. Rather than listing all Year and Month in advance. Is there any possibilities that limited it to report only the existing months listed in "T" table.  In other terms, If I include Nov 2022 data sections in "T" table (data source) for this month, I need to manully update the 2022, Nov in "T_FullMonths" table. Just looking for an alternate in that particular method. Any guidance will be appreciated.

[daXtreme]

Hi @Anonymous 

The best and easiest way to do it is to... identify the missing pieces in Power Query (should not be hard and definitely easier than in DAX!) and create a table with them in there. Then import this table into Power BI together with others, the originals. If you do that, your DAX will not only be fast and SIMPLE but you'll be able to code against the model with ease 🙂 Trust me, I know what I'm saying.

If I get a bit of time, I'll play with this and write/generate the M code needed to achieve the above. But you should try to do it yourself as well 🙂 You'll then learn the power of M.

Cheers.

[Anonymous]

I appreciate your suggestions and the way you motivate @daXtreme . Almost tried to complete by learning on my own with some partial output. Thank you for taking the time to provide a referral.