Forum Discussion

KLRK's avatar
KLRK
Frequent Visitor
4 years ago

Using different time intellingence functions for nested measures

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!

4 Replies