cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Running totals by items with date slicer

Hallo, I'm a new one in Power Bi, and lokking for support!!!
I have issue regarding running totals previous month.

How can I get correct data for each item per location and in correct period

I have ised such formula:

Q **bleep** Previous M =
VAR TimeTravel =
CALCULATE ( MAX ( Dates[Date] ), DATEADD ( Dates[Date].[Date], -1, MONTH ) )

RETURN

SUMX(

FILTER (
ALL ( Dates ),
Dates[Date]<= TimeTravel
),
[Q]
)

But this one is incorect, tha data for each items for some of them doesn't work correctly, and total is incorect as well.

G.

1 ACCEPTED SOLUTION
Helper III

Hi, finally that one formula works perfectly

Q **bleep** Previous M =
VAR Currentmonth = MAX(Dates[Date])
VAR PreviousMon = EOMONTH( Currentmonth,-1 )
RETURN
CALCULATE(sum(ITEM_Ledger_entry[Quantity]), Dates[Date]<=PreviousMon
)
14 REPLIES 14
Community Support

Hi, @GINMED ;

Try it.

``````Q cum cum Previous M =
CALCULATE(sum(ITEM_Ledger_entry[Quantity]),
FILTER(
ALL('ITEM_Ledger_entry'),
[Posting_Date] <=EOMONTH( MAX(Dates[Date]),-1
)))``````

The final output is shown below:

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.

Helper III

Hi, finally that one formula works perfectly

Q **bleep** Previous M =
VAR Currentmonth = MAX(Dates[Date])
VAR PreviousMon = EOMONTH( Currentmonth,-1 )
RETURN
CALCULATE(sum(ITEM_Ledger_entry[Quantity]), Dates[Date]<=PreviousMon
)
Helper III

Hi, Thank you! this one works correctly per item for all of lacations, but when I want to see by correct location, tha data is incorrect again.

Community Support

Hi, @GINMED ;

I'm sorry that I don't know that your demand is the total amount of last month. There is no date column in this table, so how to get the data of last month? Or could you please tell me what result you want to output?

Best Regards,
Community Support Team_ Yalan Wu

Helper III

I use the Dates table which has realtionship with Item ledger Entry (Posting date).

I need cumulative data for previous Month per each item. I already have cumulative data for current period, but I need to find prevous month data:(

I already have tried many formulas, but none of them is correct:(

Community Support

Hi, @GINMED ;

Try it.

``````Q **bleep** Previous M =
IF (
ISINSCOPE ( 'ITEM_Ledger_entry'[Year] ),
CALCULATE (
SUM ( 'ITEM_Ledger_entry'[Quantity] ),
FILTER (
ALLSELECTED ( 'ITEM_Ledger_entry' ),
EOMONTH ( [Posting_Date], 0 )
= EOMONTH ( MAX ( 'ITEM_Ledger_entry'[Posting_Date] ), -1 )
)
),
CALCULATE (
SUM ( 'ITEM_Ledger_entry'[Quantity] ),
FILTER (
ALLSELECTED ( 'ITEM_Ledger_entry' ),
EOMONTH ( [Posting_Date], 0 )
<> EOMONTH ( MAXX ( ALLSELECTED ( ITEM_Ledger_entry ), [Posting_Date] ), 0 )
)
)
)
``````

The final output is shown below:

Or

``````Q **bleep** Previous M2 =
IF (
ISINSCOPE ( 'ITEM_Ledger_entry'[Year] ),
CALCULATE (
SUM ( 'ITEM_Ledger_entry'[Quantity] ),
FILTER (
ALL ( 'ITEM_Ledger_entry' ),
EOMONTH ( [Posting_Date], 0 )
= EOMONTH ( MAX ( 'ITEM_Ledger_entry'[Posting_Date] ), -1 )
)
),
CALCULATE (
SUM ( 'ITEM_Ledger_entry'[Quantity] ),
FILTER (
ALL ( 'ITEM_Ledger_entry' ),
EOMONTH ( [Posting_Date], 0 )
<> EOMONTH ( MAXX ( ALLSELECTED ( ITEM_Ledger_entry ), [Posting_Date] ), 0 )
)
)
)``````

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.

Helper III

Tha data for this formula is empty

Community Support

Hi, @GINMED ;

Like below:

https://community.powerbi.com/t5/Desktop/Get-last-value-from-another-table/td-p/2302083

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.

Helper III

Community Support

Hi, @GINMED ;

Perhaps it would be better to provide a file with sensitive information removed.

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.

Helper III

How can I put this file here? maybe I can sent it for you by email?

WBR, Ginta

Community Support

Hi, @GINMED ;

Try it.

``````Q **bleep** Previous M =
SUMX (
FILTER ( ALL ( Dates ), EOMONTH ( [Date], 0 ) = EOMONTH ( MAX ( [Date] ), -1 ) ),
[Q]
)
``````

If this does not apply to your data, can you share a simple result after removing sensitive information and hoping to output?

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.

Helper III

Hi, thank you!

but this one does not work

I want to find the correct cumulative formula for Q  previous period

Helper III

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.