Using different time intellingence functions for nested measures

[KLRK]

Hi everyone,

I have a problem with conflicting time intelligence functions.

In my report I'd like to show the revenue of the last X months + their respective previous month revenue from one filtered date.

EXAMPLE for last 12 months

Measure1 = SUM('Orders'[Revenue]) 
Measure2 = 
    CALCULATE (
        [Measure1],
        DATESINPERIOD ('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH)
    )
CalculatedColumn = RELATED('Calendar'[Month]) & " " & RELATED('Calendar'[Year])

Dragging Measure2 as Values and CalculatedColumn as Axis on a barchart makes a nice graph showing the revenue for each of the last 12 month.

I now want to have a side to side comparison with the revenue from the respective previous month.

//add Measure for sum of previous month

Measure3 = CALCULATE(Measure1, PARALLELPERIOD('Calendar'[Date], -1 , MONTH))
Measure4 =
CALCULATE (
[Measure2],
DATESINPERIOD ('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH)
)

Dragging this measure as additional Values on the same chart is showing the sum of revenue in the actual month though and not as previous month value for a corresponding month.

Meaning that now there are Values for 13 month on my chart. With 2 times the same values for the inner month of the 13 and 1 bar for each of the first ans last month, coming from the two different measures.

In the end I also want to calculate and visualize the delta (eg. Revenue Month n - Revenue month n-1). Right now this would end up  with a funny graph where all the inner month equal out to 0 and first and last month the delta to 0.

How can I solve this issue w/o going back to check the last 12 month one by one in my filters.

I appreciate any kind of help.

Thanks!!

PS: I can provide a .pbix with the demo data if needed!

[parry2k]

@KLRK you have to wrap DATEINPERIOD in KEEPFILTERS

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[KLRK]

@parry2kcould you elaborate at which point?

A function like the one below won't do the job but rather do the opposite:

Measure4_SumOfRevenuePrevMonthLast12Month = 
    CALCULATE (
        [Measure3_SumOfRevenuePrevMonth],
        KEEPFILTERS(DATESINPERIOD ('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))
    )

[parry2k]

@KLRK why you are using the calculated column on the x-axis, you should be using it from the calendar table?

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[KLRK]

Hi!

If I use it from the calendar table it will show one bar for October with the aggregated data of the last 12 month.