Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Sarahdobbie
Frequent Visitor

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

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_0-1694618943906.png

 

3 REPLIES 3
Sarahdobbie
Frequent Visitor

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_0-1694674722013.png

 

 

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

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors