Start of the Month Running Total

[Anonymous]

Working with some data and need help calculating the Start of the month running total.

For example, if  May start of month says 9,000 kg and May yarn orders through May 31 totals 9,000 kg, than June start of month should have 18,000 kg. I will need that 9000+9000 carry over to the next start of month:

Here is the formula I am working with any help will be greatly appreciated.

Yarn Running total +1Mo. = CALCULATE(SUM('Yarn Dax'[Yarn KG]),FILTER(all('Yarn Dax'[Invoice_Date]),'Yarn Dax'[Invoice_Date]<=MAX('Yarn Dax'[Invoice_Date]) && 'Yarn Dax'[Invoice_Date]=STARTOFMONTH('Yarn Dax'[Invoice_Date])))

Thank you in Advance

[Anonymous]

Thanks in advance if someone has some ideas!!!

[Anonymous]

[v-xicai]

Hi @Anonymous ,

You may create measure like DAX below.

Yarn Running total +1Mo. =
CALCULATE (
    SUM ( 'Yarn Dax'[Yarn KG] ),
    FILTER (
        ALL ( 'Yarn Dax'[Invoice_Date] ),
        'Yarn Dax'[Invoice_Date] <= MAX ( 'Yarn Dax'[Invoice_Date] )
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Why should this =SUM('Yarn Dax'[Yarn KG]) not work?  Ensure that you have a Calendar Table with a relationship to the Invoice Date column of the Yarn DAX table.  To your visual/slicer, drag Year/Month/Date from the Calendar Table.

[Anonymous]

thanks for the reply,

the start of month usually need to carry over the previous months leftover, therefore, sum wont work

[Ashish_Mathur]

Hi,

Share some data which can be pasted in MS Excel.  Please also show the expected result.

[vivran22]

@Anonymous 

If your issue is unresolved, then please share a sample data/pbix file with expected results.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[vivran22]

Hello @Anonymous 

You may try this:

Yarn Running total +1Mo. =
CALCULATE (
    SUM ( 'Yarn Dax'[Yarn KG] ),
    FILTER (
        ALL ( 'Yarn Dax'[Invoice_Date] ),
        'Yarn Dax'[Invoice_Date] <= MAX ( 'Yarn Dax'[Invoice_Date] )
            && 'Yarn Dax'[Invoice_Date] >= STARTOFMONTH ( 'Yarn Dax'[Invoice_Date] )
    )
)

Alternatively you can also try the time intelligence funtion:

Yarn Running total +1Mo. =
CALCULATE (
    SUM ( 'Yarn Dax'[Yarn KG] ),DATESMTD[[Invoice_Date] )
      
)

Hope this helps!

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter