Running totals by items with date slicer

[GINMED]

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.

[v-yalanwu-msft]

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.

[GINMED · Accepted solution]

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

)

[GINMED]

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.

[v-yalanwu-msft]

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

[GINMED]

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:(

[v-yalanwu-msft]

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.

[GINMED]

Tha data for this formula is empty 

[v-yalanwu-msft]

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.

[GINMED]

You may download my PBI file from here.

[v-yalanwu-msft]

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.

[GINMED]

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

WBR, Ginta

[v-yalanwu-msft]

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.

[GINMED]

Hi, thank you! 

but this one does not work

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

[GINMED]