Forum Discussion
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.
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) )
3 Replies
- AlB
Community Champion
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.
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
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) )