Calculating frequency for EVERY MONTH, where values are present only in some months

[Mateja]

Hi 

I am hoping you can help me!

I am working on a HSE Dashboard and need to make a graph showing frequencies of LTA (Lost time accidents) for every month 

The formula to calculate LTA is   = Divide(SUM(_LTA[Count])*1000000, Hours[AllHours])

So to calculcate a value of LTAFreq for a certain month, one looks at last 12 months - sums the count of LTAs*1000000 and sums all working hours. Dividing that will give frequency.

Formula works fine, the values are as they should be, but only for the last month/current month 

Because there are mssing values in certain month in regards to count of LTAs it will calculate frequencies only for those months.

How can I force power bi to make calulations of frequencies for every month, not just those whwere LTAs appear.

This problem is easily solveable in excel because I appiying the mentioned formula to every cell in frequency column will automaticaly calculate it for evey month (LTA frequencies in mock table were calculated in excel)

Of course because the of limited data I mostly want frequencies for months of 2022

My LTAs are stored in master Excel in tabluar view where every line is one report with occurence date and other info.

Working hours are pulled from our SQL server.

They are not in the same table. 

I realy hope you can help me because I do not have enough experience with DAX to find a solution!

Thank you!

Mock data for the issue with last column being the values I want  :

Date	Working hours	LTA	LTAFreq I want!
2021-01-01	147854	1
2021-02-01	197966
2021-03-01	197029
2021-04-01	136530
2021-05-01	193221
2021-06-01	163444
2021-07-01	143231
2021-08-01	182706
2021-09-01	162536	1
2021-10-01	199410
2021-11-01	143552
2021-12-01	135489		1,00
2022-01-01	150896		0,50
2022-02-01	143886	1	1,02
2022-03-01	140970		1,05
2022-04-01	150705		1,05
2022-05-01	170472	1	1,59
2022-06-01	138908		1,61
2022-07-01	159559		1,60
2022-08-01	158142		1,62
2022-09-01	149305		1,09
2022-10-01	153351		1,11
2022-11-01	135956		1,12
2022-12-01	192944		1,08

[v-yalanwu-msft · Accepted solution]

Hi, @Mateja ;

I am not very clear about your logic and how to get the desired output. You mentioned that Excel can achieve the result, right? Can you share Excel and its formulas?

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mateja]

HI 

Thank you for taking time to respond.

I found the poblem in my date calendar that prevented me from using the function Datesinperiod. Once I removed the mistake, I was able to use this function to get calculations for every month.