MTD Measures on one graph / tab

[Anonymous]

Hi,

I build a powerbi report and I'm trying to properly using time intelligence functions.

I created a date table and linked it to my data table on which I added a calculated column to get date for each row.

Now I have different measures :

Units Sold = CALCULATE(SUM(SALESSKU[_AMT]);SALESSKU[_MSR]="PUS")

Units Delivered = [Units Free] + [Units Sold]

Units MTD N = TOTALMTD([Units Delivered];'Date'[Date])

Units MTD N-1 = CALCULATE([Units MTD N];SAMEPERIODLASTYEAR('Date'[Date]))

I then apply a page level filter on Year = 2019 and correctly get my figures for 2019 and 2018.

The first problem is I cannot display Units Delivered on one line graph with both years if the page level filter is active as it will only display 2019. If I select Year = 2019 or Year = 2018 on page level filter it works and I get a line for each year.

The second problem is that I have strange behavious on some tab. For example on a grid where I have each month on rows and MTD N and MTD N-1 as measures the Total won't be correct (MTD N - 1 Total is December and MTD N Total is void) with a page level filter set as Year = 2019.

Is there anything I'm doing wrong here ?

Thanks for your help.

[AlB]

Hi @Anonymous 

Can you share the pbix? A version with dummy data that reproduces the problem would do. Otherwise it's a bit hard to find out what the issue might be.

[Anonymous]

Hi @AlB ,

I made a dummy report to show my issue. You can find it here : https://ufile.io/7ri6t

As I said, I set a page level filter to 2019 and display 3 measures in the grid. As you can see the totals are not computed and badly computed.

[AlB · Accepted solution]

@Anonymous 

What is the code for  [Units Free] ??

You can fix the first issue by removing the page level filter and applying a visual level filter on the matrix visual only ('Date'[Year]=2019). In that way, the line chart, with 'Date'[Year] in legend, will show a line for 2018 and another one for 2019. 

As for the Totals, the behavior you are seeing is correct and as expected. MTD N - 1 Total shown is December because that's the latest month for 2018 so that's the one picked up by TOTALMTD when no filter is active on MonthNameLong.  

Same goes for MTD N Total. It is blank because there are no sales in December 2019.

If you want a behavior different from that you would have to tell the Total row apart from the others. You can build a new measure with something along these lines, using ISFILTERED:  

Units Sold MTD TY (with diff Total behavior) = 
IF(ISFILTERED('Date'[MonthNameLong]);
     [Units Sold MTD TY];      //Your current measure for the non-total rows
     [Measure for the total]   //Insert here what you want to do at the Total row (measure or code directly)
)