cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Lichar Regular Visitor
Regular Visitor

MTD Measures on one graph / tab

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: MTD Measures on one graph / tab

@Lichar 

 

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)
)

 

 

 

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: MTD Measures on one graph / tab

Hi @Lichar 

 

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.

 

Lichar Regular Visitor
Regular Visitor

Re: MTD Measures on one graph / tab

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.

Super User III
Super User III

Re: MTD Measures on one graph / tab

@Lichar 

 

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)
)

 

 

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors