Search code examples
excelpowerbipowerpivotdax

sum two values of dimension but show the result only in one of them in PowerPivot / DAX

It might be a easy one but I cannot solve it so please advice.

I would like to add up two months in a measure (like the current and the previous one) but when I represent the figure in a pivot table I want to show the result only in a current month.

Here is the formula I tried: 

CALCULATE([Revenue],FILTER(Tbl_Period,Tbl_Period[Month_nr] = 3)) +CALCULATE([Revenue],FILTER(Tbl_Period,Tbl_Period[Month_nr] = 4))

But logically when I represent it in pivot table the revenue is appear under the two months:

See the picture of the result of the below formula:

What I want to achieve instead is the following:

See the picture of the aimed situation:

I already managed to solve with the ALL function to show the value in all the months but not only for July as I wanted. Here is the formula for that one:

Revenue:

=CALCULATE([Revenue],FILTER(ALL(Tbl_Period[Month_nr],Tbl_Period[Period_id]),Tbl_Period[Month_nr] = 3)) +CALCULATE([Revenue],FILTER(ALL(Tbl_Period[Month_nr],Tbl_Period[Period_id]),Tbl_Period[Month_nr] = 4))

Thanks in advance for the help!

Solution

  • I'm a little bit blind, since you haven't mentioned the structure of your Tbl_Period table.

    There are many helpful functions built into that can save you a lot of work if you have a date table. One that would be relevant here is PREVIOUSMONTH().

    Working with just the information you have provided, you can get a measure to show the previous month's revenue like this:

    LastMonthRevenue: = CALCULATE([Revenue], FILTER(ALL(Tbl_Period), Tbl_Period[Month_Nr] = MAX(Tbl_Period[Month_Nr]) - 1))

    and then

    TwoMonthsRevenue: = [Revenue] + [LastMonthRevenue]