Count Months of No Recorded Data

[ghutchins]

I request help with a DAX measure to count the months that there was 0 or null risks per each site.  If there are 1 or more then it is not counted.  If there are 0 or a missing month it would be counted, requirement is to have at least 1 every month of the fiscal year.gard to 

Our fiscal year  starts on 9/1/2022.  Any help is greatly appreciated.

• Site 1- Count should be '2' since there is no data entered for 9/2022 and '0' for 12/2022
• Site 2 - Count should be '2' since there is no data entered for 10/2022 and no data entered for 1/2023, even though 2 entries are recorded for 12/2022
• Site 3 - Count should be '1' since there is no data entered for 9/2022

[ghutchins]

@Anonymous   I would like to send you the PBIX but the data is confidential.  It is however derived from a sharepoint list that has date the Risk Assessment is done, fiscal year, status, Site (where assessment is done), and  all the usual SPO List data.  The table above basically outlines the expected results. 

Short story i need to look at all the sites individually month-by-month and determine if someone has entered an assessment for that month for each site.   If a no entry is made for a month or a 0 is entered I must count that month. If Risk > 0 i do not count that month for that site.

[Anonymous]

Hi @ghutchins,

It seems like you do not have enough permission to upload pbix file as attachment.

For this scenario, you can create some dummy data on excel side with current structure and replace/remove sensitive data to paste the excel table in your reply with table format.

After these steps, we can simply copy and enter these dummy data to power bi desktop to test and coding formulas.

Notice:

My formula logic is creating a variable table with all year month range based on the current side date start/end date.

Then you can use EXCEPT function to compare two list to get missed records and its count, add the result with filter raw list item count to get finial results.

Regards,

Xiaoxisn Sheng

[Anonymous]

Hi @ghutchins,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng