how to add last two month column data dynamically based on item number

[sridharbabu]

for eg in nov 23 for product A having order of 50,

in dec 23 for product A having order of 45

output:

in jan 24 for product A order should be 50 + 40=90 (nov23 product A order + dec 23 product A order).

It has to go dynamically for feb 24, jan24+dec 23.

how to achieve this in power bi

[amustafa]

It would help if you can provide a sample data and variations of date formats in your table.

[sridharbabu]

sample data

Product	location	monthyear	order	MonthSeq
3245	abc	Nov-23	34	23
3245	abc	Oct-23	56	22
5255	xyz	Nov-23	45	23
5255	xyz	Oct-23	96	22

[Anonymous · Accepted solution]

Hi @sridharbabu 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Set the date type you need, for example: mmmm d.

Create a measure. Calculate the sum of orders in the last two months.

total order = 
    var max_date = CALCULATE(MAX('Table'[monthyear]), FILTER(ALL('Table'), 'Table'[Product] = MAX('Table'[Product])))
    var start_date = EDATE(max_date, -2)
return 
    CALCULATE(
        SUM('Table'[order]), 
        FILTER(
            ALL('Table'), 
            'Table'[Product] = MAX('Table'[Product]) 
            &&
            'Table'[monthyear] >= start_date 
            &&
            'Table'[monthyear] < max_date))

Here is the result.

Regards,

Nono Chen

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

[sridharbabu]

In continuation for above question, how can i display dynamically the previous two months data in table in power bi. loading data from sql to power bi. Every month add data to sql table. Dynamically has to show previous two months of data as table in power bi.

[sridharbabu]

Date in table is of string format like Nov-23. How to use it.

[amustafa]

Following DAX measure should correctly calculate the sum of orders for the two months preceding the month of the selected date. 

Last Two Months Orders =

VAR SelectedDate = SELECTEDVALUE(MyTable[Date])

VAR FirstDayOfSelectedMonth = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1)

VAR StartOfPreviousTwoMonths = EOMONTH(FirstDayOfSelectedMonth, -3) + 1

VAR EndOfPreviousTwoMonths = EOMONTH(FirstDayOfSelectedMonth, -1)

RETURN

CALCULATE(

    SUM(MyTable[Order]),

    FILTER(

        ALL(MyTable),

        MyTable[Date] >= StartOfPreviousTwoMonths && MyTable[Date] <= EndOfPreviousTwoMonths

    )

)