How to calculate sum of a value over a period of 3 months + Current MTD & calculate share of an item

[Sarahdobbie]

Hi,

Hi have a table with a column with 14 months of data on parts. For my report I need to calculate the total of last 3 months + Month to data for the confirmed lines, Allocated lines. I also need to calculate the missing lines & the share of the missing lines of the total Confirmed lines that month (so not on the total of the last 3 months + month to date).

Next to that, when a specific month is selected the data must only reflect that months data and share.

I have managed to calculate the sum of the Confirmed lines, Missing lines with Calculate formulas, but the share is never represented in a correct way in the tabel.

I have tried several different ways, but I can't seem to find the correct way.

I welcome every input.

Below the desired outcome, and this needs to be reflected when only selecting 1 month are any other selection chosen (Part for example). This is an example in BI the conf lines and Alloc lines will not be shown.

[Sarahdobbie]

Thanks @Greg_Deckler ,

I have stripped the data as it is huge, but basically this is what I need to work on:

PART DATE Conf. Line Allocated Lines
A 2/06/2023 0:00 1 1
A 11/06/2023 0:00 1 1
A 23/06/2023 0:00 1 1
A 12/07/2023 0:00 1 1
A 31/07/2023 0:00 1 1
A 4/08/2023 0:00 1 1
A 23/08/2023 0:00 1 1
A 1/09/2023 0:00 1 1
A 4/09/2023 0:00 1 0
B 2/06/2023 0:00 1 1
B 11/06/2023 0:00 1 1
B 23/06/2023 0:00 1 1
B 12/07/2023 0:00 1 1
B 31/07/2023 0:00 1 0
B 4/08/2023 0:00 1 1
B 23/08/2023 0:00 1 1
B 1/09/2023 0:00 1 1
B 4/09/2023 0:00 1 1
C 2/06/2023 0:00 1 1
C 11/06/2023 0:00 1 0
C 23/06/2023 0:00 1 1
C 12/07/2023 0:00 1 0
C 31/07/2023 0:00 1 1
C 4/08/2023 0:00 1 1
C 23/08/2023 0:00 1 1
C 1/09/2023 0:00 1 0
C 4/09/2023 0:00 1 1

With this data I need to calculate the number of missing lines per part and I need to display the results in a table, displaying the missing lines and the share these missing lines have per part on the total confirmed lines of that specific month.

I also need to calculate the total of the last 3 month + current month to date and the total needs to display the share of the part on the total confirmed lines over the period.

Above I need to share the data of the confirmed lines over the total period.

I managed to get the total over the period, but when putting it in the tabel I can't seem to get the correct share over the total or per month (it seems to be on or the other which is correct).

I have tried calculate in combination with dates MTD, Datesbetween, no date logic in the dax, but can't seem to get the correct numbers.

I am very new to Power Bi, so I am a bit lost. Hope the above explains the issue better? 

[Sarahdobbie]

Oh, And I only need to display the 10 worst parts. So the real totals are never displayed in the table.

[Greg_Deckler]

@Sarahdobbie You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Otherwise: Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.